Attribute VB_Name = "modFSMAPI"
'
' Ship FedEx Express via FSM API and receive the PNG label.
'
' Requires installed FSM API.
'
' 2010-07-29, JFW, My version has formatting and recognition of optional columns.
'                  Inserted ZP's MPS code.
'

Option Explicit

Dim xmlDoc As DOMDocument
Dim xmlReply As DOMDocument
Dim xmlMail As DOMDocument

Dim strTrans As String
Dim strReply As String
Dim strLabelFilename As String
Dim strTrackingNbr As String
Dim strNetRate As String
Dim strZip As String
Dim strCountry As String
Dim strOption() As String

Public g_objFSO As FileSystemObject
Public g_objSettings As classSettings

Public mblnStop As Boolean

Dim mobjWorksheet As Worksheet

Const FORMAT_AMOUNT As String = "_(* #,##0.00_);_(* (#,##0.00);_(* ""-""??_);_(@_)"
Const FORMAT_DATEONLY As String = "yyyy-mm-dd;@"
Const FORMAT_NUMERIC As String = "0"

Dim lngShipDate As Long ' Column
Dim mdtShipDate As Date ' Specific date shipping
Dim boolShipDate As Boolean ' Specific date is in effect

' MPS
Dim lngPackageCount As Long
Dim lngPackageNumber As Long
'


Public Sub APIBatchAddress()

    Dim objWorkbook As Workbook
    Dim i As Long
    
    mblnStop = False
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    Set objWorkbook = ActiveWorkbook
    Set mobjWorksheet = ActiveSheet
    
'    For Each objWorkbook In Workbooks
    
'        For Each mobjWorksheet In objWorkbook.Sheets
            
            i = CLng(g_objSettings.Setting("HEADER_ROW")) + 1
            Debug.Print mobjWorksheet.Name
            Do
                If SheetColumn(g_objSettings.Setting("SHEET_CONTACT")) < 1 Then
                    Exit Do
                End If
                If mobjWorksheet.Cells(i, SheetColumn(g_objSettings.Setting("SHEET_CONTACT"))) = "" Then Exit Do
                If mobjWorksheet.Cells(i, SheetColumn("ValidationData")) = "" Then
                    Call APIBatchAddressOne(i)
                End If
                i = i + 1
                DoEvents
                If mblnStop Then Exit Do
            Loop
            
'            Exit For
'        Next mobjWorksheet
        
        ' Exit For
'    Next objWorkbook
    
End Sub


Public Sub APIBatchAddressOne(lngRow As Long)

    On Error GoTo ErrorExit
    
    Dim obj As Object
    Dim xmlnode1 As IXMLDOMNode
    Dim xmlNode2 As IXMLDOMNode
    Dim xmlNode3 As IXMLDOMNode
    Dim xmlNode4 As IXMLDOMNode
    Dim xmlNode5 As IXMLDOMNode
    Dim xmlNode6 As IXMLDOMNode
    Dim xmlNode7 As IXMLDOMNode
    
    Dim strTrans As String
    Dim j As Long
    Dim strWeight As String
    
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject

    Set xmlDoc = New DOMDocument

    strCountry = CountryConv(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_COUNTRYCODE"))))

    With xmlDoc.appendChild(xmlDoc.createElement("ns:AddressValidationRequest"))
        Set obj = xmlDoc.createAttribute("xmlns:ns")
        obj.Text = "http://fedex.com/ws/addressvalidation/v2"
        Call .Attributes.setNamedItem(obj)
        
        Set obj = xmlDoc.createAttribute("xmlns:xsi")
        obj.Text = "http://www.w3.org/2001/XMLSchema-instance"
        Call .Attributes.setNamedItem(obj)
        
'        Set obj = xmlDoc.createAttribute("xsi:schemaLocation")
'        obj.Text = "http://fedex.com/ws/rate/v5/RateService_v5.xsd"
'        Call .Attributes.setNamedItem(obj)
        
        With .appendChild(xmlDoc.createElement("ns:WebAuthenticationDetail"))
            With .appendChild(xmlDoc.createElement("ns:UserCredential"))
                .appendChild(xmlDoc.createElement("ns:Key")).Text = g_objSettings.Setting("WS_KEY")
                If g_objSettings.Setting("WS_PASSWORD") <> "" Then
                    .appendChild(xmlDoc.createElement("ns:Password")).Text = g_objSettings.Setting("WS_PASSWORD")
                End If
            End With
        End With
        With .appendChild(xmlDoc.createElement("ns:ClientDetail"))
            .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
            .appendChild(xmlDoc.createElement("ns:MeterNumber")).Text = g_objSettings.Setting("METER_NUMBER")
        End With
        With .appendChild(xmlDoc.createElement("ns:TransactionDetail"))
            .appendChild(xmlDoc.createElement("ns:CustomerTransactionId")).Text = lngRow
        End With
        With .appendChild(xmlDoc.createElement("ns:Version"))
            .appendChild(xmlDoc.createElement("ns:ServiceId")).Text = "aval"
            .appendChild(xmlDoc.createElement("ns:Major")).Text = "2"
            .appendChild(xmlDoc.createElement("ns:Intermediate")).Text = "0"
            .appendChild(xmlDoc.createElement("ns:Minor")).Text = "0"
        End With
        .appendChild(xmlDoc.createElement("ns:RequestTimestamp")).Text = Format(Now, "yyyy-mm-dd") & "T" & Format(Now, "HH:nn:ss") & "-05:00"
        With .appendChild(xmlDoc.createElement("ns:Options"))
            .appendChild(xmlDoc.createElement("ns:MaximumNumberOfMatches")).Text = 5
        End With
        With .appendChild(xmlDoc.createElement("ns:AddressesToValidate"))
            ' .appendChild(xmlDoc.createElement("ns:AddressId")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_REFERENCE")))
            With .appendChild(xmlDoc.createElement("ns:Address"))
                .appendChild(xmlDoc.createElement("ns:StreetLines")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS1")))
                If g_objSettings.Setting("SHEET_ADDRESS2") <> "" Then
                    If mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS2"))) <> "" Then
                        .appendChild(xmlDoc.createElement("ns:StreetLines")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS2")))
                    End If
                End If
                .appendChild(xmlDoc.createElement("ns:City")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_CITY")))
                .appendChild(xmlDoc.createElement("ns:StateOrProvinceCode")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_STATE")))
                .appendChild(xmlDoc.createElement("ns:PostalCode")).Text = ZipConv( _
                                strCountry, _
                                mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ZIP"))))
                .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = strCountry
            End With
        End With
    End With

    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & lngRow & " AddressRequest.xml", True)
        .Write xmlDoc.FirstChild.XML
        .Close
    End With

    g_strConfigHostEnvironment = g_objSettings.Setting("API_ENVIRONMENT")
    strTrans = SendRequestToGateway(xmlDoc.FirstChild.XML)
    
    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & lngRow & " AddressReply.xml", True)
        .Write strTrans
        .Close
    End With
    Set xmlReply = New DOMDocument
    xmlReply.loadXML strTrans

    ' mobjWorksheet.Cells(lngRow, SheetColumn("ValidationData")) = strTrans

    For Each xmlnode1 In xmlReply.selectNodes("v2:AddressValidationReply")
        For Each xmlNode2 In xmlnode1.selectNodes("v2:AddressResults")
                For Each xmlNode3 In xmlNode2.selectNodes("v2:ProposedAddressDetails")
                
                    For Each xmlNode4 In xmlNode3.selectNodes("v2:Score")
                        mobjWorksheet.Cells(lngRow, SheetColumn("Score")) = xmlNode4.Text
                        Exit For
                    Next
                    For Each xmlNode4 In xmlNode3.selectNodes("v2:Changes")
                        mobjWorksheet.Cells(lngRow, SheetColumn("Changes")) = mobjWorksheet.Cells(lngRow, SheetColumn("Changes")) & IIf(mobjWorksheet.Cells(lngRow, SheetColumn("Changes")) <> "", ", ", "") & xmlNode4.Text
                    Next
                
                    For Each xmlNode4 In xmlNode3.selectNodes("v2:Address")
                        mobjWorksheet.Cells(lngRow, SheetColumn("RevStreetLines")) = ""
                        For Each xmlNode5 In xmlNode4.selectNodes("v2:StreetLines")
                            mobjWorksheet.Cells(lngRow, SheetColumn("RevStreetLines")) = mobjWorksheet.Cells(lngRow, SheetColumn("RevStreetLines")) & IIf(mobjWorksheet.Cells(lngRow, SheetColumn("RevStreetLines")) <> "", ", ", "") & xmlNode5.Text
                        Next
                        For Each xmlNode5 In xmlNode4.selectNodes("v2:City")
                            mobjWorksheet.Cells(lngRow, SheetColumn("RevCity")) = xmlNode5.Text
                        Next
                        For Each xmlNode5 In xmlNode4.selectNodes("v2:StateOrProvinceCode")
                            mobjWorksheet.Cells(lngRow, SheetColumn("RevStateProv")) = xmlNode5.Text
                        Next
                        For Each xmlNode5 In xmlNode4.selectNodes("v2:PostalCode")
                            mobjWorksheet.Cells(lngRow, SheetColumn("RevPostalCode")) = xmlNode5.Text
                        Next
                    Next
                
                    Exit For
                    
                Next
        Next
    Next


    Exit Sub

ErrorExit:
    mobjWorksheet.Cells(lngRow, SheetColumn("ValidationError")) = Err.Source & " - " & Err.Description
    If False Then
        Resume
    End If

End Sub


Public Sub APIBatchAddressOld(lngRow As Long)
    
    On Error GoTo ErrorExit
    
    Dim strTrans As String
    Dim j As Long
    
    'Set xmlDoc = MakeRoot("FDXAddressVerificationRequest")
    
    With xmlDoc.FirstChild
        With .appendChild(xmlDoc.createElement("RequestHeader"))
            .appendChild(xmlDoc.createElement("CustomerTransactionIdentifier")).Text = "1234"
            .appendChild(xmlDoc.createElement("AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
            .appendChild(xmlDoc.createElement("MeterNumber")).Text = g_objSettings.Setting("METER_NUMBER")
            .appendChild(xmlDoc.createElement("CarrierCode")).Text = "FDXE"
        End With
        .appendChild(xmlDoc.createElement("CompanyName")).Text = "Sender Company"
        .appendChild(xmlDoc.createElement("Line1")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS1")))
        .appendChild(xmlDoc.createElement("City")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_CITY")))
        .appendChild(xmlDoc.createElement("StateOrProvinceCode")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_STATE")))
        .appendChild(xmlDoc.createElement("PostalCode")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ZIP")))
        ' .appendChild(xmldoc.createElement(SHEET_COUNTRYCODE)).Text = "CA"
    End With
    
    xmlDoc.Save "c:\temp.xml"

    strTrans = SendRequestToGateway("<?xml version=""1.0"" encoding=""UTF-8"" ?> " & xmlDoc.FirstChild.XML)

    Set xmlReply = New DOMDocument
    xmlReply.loadXML (strTrans)
    
    If xmlReply.selectSingleNode("FDXAddressVerificationReply") Is Nothing Then
        ' Ground error
        If Not xmlReply.selectSingleNode("Error") Is Nothing Then
            mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = _
                xmlReply.selectSingleNode("FDXAddressVerificationReply").selectSingleNode("Error").selectSingleNode("Message").Text
        Else
            mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = strTrans
        End If
        
    ElseIf Not xmlReply.selectSingleNode("FDXAddressVerificationReply").selectSingleNode("Match") Is Nothing Then

        If Not xmlReply.selectSingleNode("FDXAddressVerificationReply").selectSingleNode("Match").selectSingleNode("SoftError") Is Nothing Then
            strTrackingNbr = xmlReply.selectSingleNode("FDXAddressVerificationReply").selectSingleNode("Match").selectSingleNode("SoftError").Text
            mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = strTrackingNbr
        Else
        End If
        
    ElseIf Not xmlReply.selectSingleNode("FDXAddressVerificationReply").selectSingleNode("Error") Is Nothing Then

        strTrackingNbr = xmlReply.selectSingleNode("FDXAddressVerificationReply").selectSingleNode("Error").selectSingleNode("Message").Text
        mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = strTrackingNbr

    Else
        ' Debug.Print strTrans
        
    End If

    Exit Sub
    
ErrorExit:
        mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = Err.Source & " - " & Err.Description

End Sub


Public Sub APIBatchMPS()

    Dim objWorkbook As Workbook
    Dim lngRow As Long, j As Long
    Dim lngShipTotal As Long
    Dim lngShipTry As Long
    Dim strShipDate As String
    Dim boolShipIt As Boolean
    Dim boolProcess As Boolean
    
    mblnStop = False
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    mdtShipDate = #11/8/2008#
    ' If shipper has ship date then must confirm
    If g_objSettings.Setting("SHEET_SHIPDATE") <> "" Then
        ' Confirm and specify date in worksheet
        lngShipDate = SheetColumn(g_objSettings.Setting("SHEET_SHIPDATE"))
        boolShipDate = True
        While mdtShipDate < Date
            strShipDate = InputBox("Please confirm the DATE of label you want to print", "API Batch date", Date)
            If strShipDate = "" Then Exit Sub
            If IsDate(strShipDate) Then mdtShipDate = CDate(strShipDate)
        Wend
    ElseIf SheetColumnExists("ShipDate") Then
        ' Allow future day shipping
        lngShipDate = SheetColumn("ShipDate")
        mdtShipDate = Date
        boolShipDate = False
    Else
        ' No date specified
        mdtShipDate = Date
        boolShipDate = False
    End If
    
    Set objWorkbook = ActiveWorkbook
    Set mobjWorksheet = ActiveSheet
    
'    For Each objWorkbook In Workbooks
    
'        For Each mobjWorksheet In objWorkbook.Sheets
            
            lngRow = CLng(g_objSettings.Setting("HEADER_ROW")) + 1
            Debug.Print mobjWorksheet.Name
            ' 2010-07-27, ZP, Add form id column for MPS
            SheetColumn ("FormId")
            Do ' each row of the sheet
                If SheetColumn(g_objSettings.Setting("SHEET_CONTACT")) < 1 Then
                    Exit Do
                End If
                If mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_CONTACT"))) = "" Then
                    Exit Do
                End If
                If mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = "" Then
                    boolProcess = False
                    If Not boolShipDate Then
                        boolProcess = True
                    ElseIf lngShipDate <= 0 Then
                        boolProcess = True
                    ElseIf IsDate(mobjWorksheet.Cells(lngRow, lngShipDate)) Then
                        If CDate(mobjWorksheet.Cells(lngRow, lngShipDate)) = mdtShipDate Then
                            boolProcess = True
                        End If
                    End If
                    If boolProcess Then

                        ' 2011-11-25, JFW, Blank notifications for attempted shipment
                        If SheetColumnExists("Notifications") Then
                            If mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) <> "" Then
                                mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = ""
                            End If
                        End If

                        ' New MPS processing
                        ' Debug.Print mobjWorksheet.Cells(lngRow, 1)
                        lngShipTry = lngShipTry + 1
                        
                        If SheetColumnExists("PackageCount") Then
                            lngPackageCount = SheetColumn("PackageCount")
                            lngPackageNumber = SheetColumn("PackageNumber")
                        ElseIf SheetColumnExists("PCS") Then
                            lngPackageCount = SheetColumn("PCS")
                            lngPackageNumber = SheetColumn("MPS_Id")
                        End If
                        
                        If lngPackageCount > 0 Then
                        If IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageCount)) Then
                        If CLng(mobjWorksheet.Cells(lngRow, lngPackageCount)) > 1 Then
                            
                            If mobjWorksheet.Cells(lngRow, lngPackageNumber) = "" Then
                                ' Pieces not specified
                                mobjWorksheet.Cells(lngRow, lngPackageNumber) = 1
                                
                                For j = 2 To CLng(mobjWorksheet.Cells(lngRow, lngPackageCount))
                                    ' Range("a" & (lngRow + j - 1)).Select
                                    mobjWorksheet.Cells(lngRow + j - 1, 1).EntireRow.Insert
                                
                                    Call APIBatchShipCopyRow(lngRow, lngRow + j - 1)
                                    
                                    ' mobjWorksheet.Cells(lngRow, 1).EntireRow.Copy mobjWorksheet.Cells(lngRow + j - 1, 1)
                                    
                                    mobjWorksheet.Cells(lngRow + j - 1, lngPackageCount) = ""
                                    mobjWorksheet.Cells(lngRow + j - 1, lngPackageNumber) = j
                                    
                                    If SheetColumnExists("Weight") Then
                                        If g_objSettings.Setting("MPS_WEIGHT") = "Total" Then
                                            mobjWorksheet.Cells(lngRow + j - 1, SheetColumn("Weight")) = 0
                                        ElseIf g_objSettings.Setting("MPS_WEIGHT") = "Edit" Then
                                            mobjWorksheet.Cells(lngRow + j - 1, SheetColumn("Weight")) = ""
                                        Else
                                            ' Parse Pack
                                            If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) Then
                                                mobjWorksheet.Cells(lngRow + j - 1, SheetColumn("Weight")) = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) / CLng(mobjWorksheet.Cells(lngRow, lngPackageCount))
                                                ' Weight allocation is complete, set 1st piece weight
                                                If j = CLng(mobjWorksheet.Cells(lngRow, lngPackageCount)) Then
                                                    mobjWorksheet.Cells(lngRow, SheetColumn("Weight")) = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) / CLng(mobjWorksheet.Cells(lngRow, lngPackageCount))
                                                End If
                                            End If
                                        End If
                                    End If
                                Next j
                                
                                If SheetColumnExists("Weight") Then
                                    If g_objSettings.Setting("MPS_WEIGHT") = "Edit" Then
                                        mobjWorksheet.Range(mobjWorksheet.Cells(lngRow, SheetColumn("Weight")), mobjWorksheet.Cells(lngRow - 1 + CLng(mobjWorksheet.Cells(lngRow, lngPackageCount)), SheetColumn("Weight"))).Activate
                                        Exit Sub
                                    End If
                                End If
                            
                            Else
                                ' Pieces specified
                                
                            End If
                        
                        End If ' Piece count > 1
                        End If ' Numeric piece count
                        End If ' PCS column
                        
                        ' Total weight
                        If lngPackageCount > 0 Then
                        If IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageCount)) Then
                        If CLng(mobjWorksheet.Cells(lngRow, lngPackageCount)) > 1 Then
                            If IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageNumber)) Then
                                mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt")) = 0
                                For j = 1 To CLng(mobjWorksheet.Cells(lngRow, lngPackageCount))
                                    If SheetColumnExists("Weight") Then
                                        If IsNumeric(mobjWorksheet.Cells(lngRow + j - 1, SheetColumn("Weight"))) Then
                                            mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt")) = mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt")) _
                                                    + mobjWorksheet.Cells(lngRow + j - 1, SheetColumn("Weight"))
                                        End If
                                    Else
                                        mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt")) = mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt")) _
                                            + g_objSettings.Setting("SHIPPING_WEIGHT")
                                    End If
                                Next j
                            End If
                        End If ' Piece count > 1
                        End If ' Numeric piece count
                        End If ' PCS column
                        
                        ' Total weight
                        If lngPackageCount > 0 Then
                        If IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageCount)) Then
                        If CLng(mobjWorksheet.Cells(lngRow, lngPackageCount)) > 1 Then
                            If IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageNumber)) Then
                                For j = 2 To CLng(mobjWorksheet.Cells(lngRow, lngPackageCount))
                                    If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt"))) Then
                                        mobjWorksheet.Cells(lngRow + j - 1, SheetColumn("TotalWgt")) = mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt"))
                                    End If
                                Next j
                            End If
                        End If ' Piece count > 1
                        End If ' Numeric piece count
                        End If ' PCS column
                        
                        
                        
                        
                        
                        boolShipIt = False
                        If lngPackageCount <= 0 Then
                            boolShipIt = True
                        ElseIf lngPackageNumber <= 0 Then
                            boolShipIt = True
                        ElseIf Not IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageCount)) Then
                            boolShipIt = True
                        ElseIf Not CLng(mobjWorksheet.Cells(lngRow, lngPackageCount)) = 1 Then
                            boolShipIt = True
                        ElseIf Not IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageNumber)) Then
                            boolShipIt = False
                        ElseIf IsNumeric(mobjWorksheet.Cells(lngRow + 1 - mobjWorksheet.Cells(lngRow, lngPackageNumber), SheetColumn(g_objSettings.Setting("TrackingNumber")))) Then
                            boolShipIt = True
                        Else
                            mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = "This row fails single/MPS testing."
                            mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")).Font.ColorIndex = 3
                            boolShipIt = False
                        End If
                        
                    End If
                End If
                lngRow = lngRow + 1
                DoEvents
                If mblnStop Then Exit Do
            Loop ' each row of the sheet
            
            ' 2010-07-27, ZP, Remove form id
            DeleteColumn ("FormId")
            ' First sheet only
'            Exit For
            
'        Next mobjWorksheet
        
        ' Exit For
'    Next objWorkbook
    
    Exit Sub
    
End Sub


Public Sub APIBatchMPSforTracking()

    Dim objWorkbook As Workbook
    Dim lngRow As Long, j As Long
    Dim lngShipTotal As Long
    Dim lngShipTry As Long
    Dim strShipDate As String
    Dim boolShipIt As Boolean
    Dim boolProcess As Boolean
    Dim strTrackingNumber() As String
    
    mblnStop = False
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    Set objWorkbook = ActiveWorkbook
    Set mobjWorksheet = ActiveSheet
    
    lngRow = CLng(g_objSettings.Setting("HEADER_ROW")) + 1
    Debug.Print mobjWorksheet.Name
    Do ' each row of the sheet
        If mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) <> "" Then
            boolProcess = False
            If 0 <> InStr(1, mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))), ",") Then
                boolProcess = True
            End If
            If boolProcess Then
                
                strTrackingNumber = Split(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))), ",")
                Debug.Print lngRow, LBound(strTrackingNumber), UBound(strTrackingNumber)
                
                lngPackageCount = UBound(strTrackingNumber) - LBound(strTrackingNumber) + 1
                lngPackageNumber = 1
                
                If lngPackageCount > 1 Then
                        
                    For j = 2 To lngPackageCount
                        ' Range("a" & (lngRow + j - 1)).Select
                        mobjWorksheet.Cells(lngRow + j - 1, 1).EntireRow.Insert
                    
                        Call APIBatchShipCopyRow(lngRow, lngRow + j - 1)
                        
                        mobjWorksheet.Cells(lngRow + j - 1, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = strTrackingNumber(j - 1)
                        mobjWorksheet.Cells(lngRow + j - 1, SheetColumn(g_objSettings.Setting("TrackingNumber"))).NumberFormat = 0
                        
                    Next j
                    
                    mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = strTrackingNumber(0)
                    mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))).NumberFormat = 0
                    
                End If ' Piece count > 1
            End If ' Process multi tracking number
        End If ' TrackingNumber
        lngRow = lngRow + 1
        DoEvents
        If mblnStop Then Exit Do
        If lngRow > 9999 Then Exit Do
    Loop ' each row of the sheet
    
    Exit Sub
    
End Sub




Public Sub APIBatchRate()

    Dim objWorkbook As Workbook
    Dim i As Long
    
    mblnStop = False
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    Set objWorkbook = ActiveWorkbook
    Set mobjWorksheet = ActiveSheet
    
'    For Each objWorkbook In Workbooks
    
'        For Each mobjWorksheet In objWorkbook.Sheets
            
            Call APIBatchMPS
            
            i = CLng(g_objSettings.Setting("HEADER_ROW")) + 1
            Debug.Print mobjWorksheet.Name
            Do
                If SheetColumn(g_objSettings.Setting("SHEET_CONTACT")) < 1 Then
                    Exit Do
                End If
                If mobjWorksheet.Cells(i, SheetColumn(g_objSettings.Setting("SHEET_CONTACT"))) = "" Then Exit Do
                If mobjWorksheet.Cells(i, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = "" Then
                    ' Debug.Print mobjWorksheet.Cells(i, 1)
                    Call APIBatchRateOne(i)
                End If
                i = i + 1
                DoEvents
                If mblnStop Then Exit Do
            Loop
            
'            Exit For
'        Next mobjWorksheet
        
        ' Exit For
'    Next objWorkbook
    
End Sub

Public Sub APIBatchRateOne(ByRef lngRow As Long)

    On Error GoTo ErrorExit
    
    Dim obj As Object
    Dim xmlnode1 As IXMLDOMNode
    Dim xmlNode2 As IXMLDOMNode
    Dim xmlNode3 As IXMLDOMNode
    Dim xmlNode4 As IXMLDOMNode
    Dim xmlNode5 As IXMLDOMNode
    Dim xmlNode6 As IXMLDOMNode
    Dim xmlNode7 As IXMLDOMNode
    
    Dim strTrans As String
    Dim j As Long
    Dim strWeight As String
    
    Dim lngPackage As Long
    Dim boolShipIt As Boolean
    
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject

    If SheetColumn("Options", True) > 0 Then
        strOption = OptionSplit(mobjWorksheet.Cells(lngRow, SheetColumn("Options")))
    Else
        ReDim strOption(0)
    End If

    Set xmlDoc = New DOMDocument

    With xmlDoc.appendChild(xmlDoc.createElement("ns:RateRequest"))
        Set obj = xmlDoc.createAttribute("xmlns:ns")
        obj.Text = "http://fedex.com/ws/rate/v5"
        Call .Attributes.setNamedItem(obj)
        
        Set obj = xmlDoc.createAttribute("xmlns:xsi")
        obj.Text = "http://www.w3.org/2001/XMLSchema-instance"
        Call .Attributes.setNamedItem(obj)
        
        Set obj = xmlDoc.createAttribute("xsi:schemaLocation")
        obj.Text = "http://fedex.com/ws/rate/v5/RateService_v5.xsd"
        Call .Attributes.setNamedItem(obj)
        
        With .appendChild(xmlDoc.createElement("ns:WebAuthenticationDetail"))
            With .appendChild(xmlDoc.createElement("ns:UserCredential"))
                .appendChild(xmlDoc.createElement("ns:Key")).Text = g_objSettings.Setting("WS_KEY")
                If g_objSettings.Setting("WS_PASSWORD") <> "" Then
                    .appendChild(xmlDoc.createElement("ns:Password")).Text = g_objSettings.Setting("WS_PASSWORD")
                End If
            End With
        End With
        With .appendChild(xmlDoc.createElement("ns:ClientDetail"))
            .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
            .appendChild(xmlDoc.createElement("ns:MeterNumber")).Text = g_objSettings.Setting("METER_NUMBER")
        End With
        With .appendChild(xmlDoc.createElement("ns:TransactionDetail"))
            .appendChild(xmlDoc.createElement("ns:CustomerTransactionId")).Text = lngRow
        End With
        With .appendChild(xmlDoc.createElement("ns:Version"))
            .appendChild(xmlDoc.createElement("ns:ServiceId")).Text = "crs"
            .appendChild(xmlDoc.createElement("ns:Major")).Text = "5"
            .appendChild(xmlDoc.createElement("ns:Intermediate")).Text = "0"
            .appendChild(xmlDoc.createElement("ns:Minor")).Text = "0"
        End With
        strCountry = CountryConv(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_COUNTRYCODE"))))
            
        .appendChild(xmlDoc.createElement("ns:ReturnTransitAndCommit")).Text = "1"
            
        With .appendChild(xmlDoc.createElement("ns:RequestedShipment"))
            If lngShipDate > 0 Then
                If IsDate(mobjWorksheet.Cells(lngRow, lngShipDate)) Then
                    .appendChild(xmlDoc.createElement("ns:ShipTimestamp")).Text = Format(CDate(mobjWorksheet.Cells(lngRow, lngShipDate)), "yyyy-mm-dd") & "T" & Format(Now, "HH:nn:ss") & "-05:00"
                End If
            Else
                .appendChild(xmlDoc.createElement("ns:ShipTimestamp")).Text = Format(Date, "yyyy-mm-dd") & "T" & Format(Now, "HH:nn:ss") & "-05:00"
            End If
            
            .appendChild(xmlDoc.createElement("ns:ServiceType")).Text = ServiceType(lngRow, strCountry)
            .appendChild(xmlDoc.createElement("ns:PackagingType")).Text = PackageType(lngRow)
            
            With .appendChild(xmlDoc.createElement("ns:TotalWeight"))
                .appendChild(xmlDoc.createElement("ns:Units")).Text = g_objSettings.Setting("WEIGHT_TYPE")
                If SheetColumnExists("TotalWgt") Then
                    If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt"))) Then
                        If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt"))) > 0 Then
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt")))
                        Else
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                        End If
                    Else
                        .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                    End If
                ElseIf SheetColumnExists("Weight") Then
                    If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) Then
                        If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) > 0 Then
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight")))
                        Else
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                        End If
                    Else
                        .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                    End If
                Else
                    .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                End If
            End With
            With .appendChild(xmlDoc.createElement("ns:Shipper"))
                With .appendChild(xmlDoc.createElement("ns:Contact"))
                    .appendChild(xmlDoc.createElement("ns:PersonName")).Text = g_objSettings.Setting("SENDER_CONTACT")
                    .appendChild(xmlDoc.createElement("ns:CompanyName")).Text = g_objSettings.Setting("SENDER_COMPANY")
                    .appendChild(xmlDoc.createElement("ns:PhoneNumber")).Text = g_objSettings.Setting("SENDER_PHONE")
                End With
                With .appendChild(xmlDoc.createElement("ns:Address"))
                    .appendChild(xmlDoc.createElement("ns:StreetLines")).Text = g_objSettings.Setting("SENDER_ADDR1")
                    .appendChild(xmlDoc.createElement("ns:City")).Text = g_objSettings.Setting("SENDER_CITY")
                    .appendChild(xmlDoc.createElement("ns:StateOrProvinceCode")).Text = g_objSettings.Setting("SENDER_STPV")
                    .appendChild(xmlDoc.createElement("ns:PostalCode")).Text = g_objSettings.Setting("SENDER_ZIP")
                    .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                End With
            End With
            With .appendChild(xmlDoc.createElement("ns:Recipient"))
                'With .appendChild(xmlDoc.createElement("ns:Contact"))
                '    .appendChild(xmlDoc.createElement("ns:PersonName")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_CONTACT")))
                '    .appendChild(xmlDoc.createElement("ns:CompanyName")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_COMPANY")))
                '    .appendChild(xmlDoc.createElement("ns:PhoneNumber")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_PHONE")))
                'End With
                With .appendChild(xmlDoc.createElement("ns:Address"))
                    .appendChild(xmlDoc.createElement("ns:StreetLines")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS1")))
                    .appendChild(xmlDoc.createElement("ns:City")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_CITY")))
                    .appendChild(xmlDoc.createElement("ns:StateOrProvinceCode")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_STATE")))
                    .appendChild(xmlDoc.createElement("ns:PostalCode")).Text = ZipConv( _
                                    strCountry, _
                                    mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ZIP"))))
                    .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = strCountry
                    If OptionTest("resi") Then
                        .appendChild(xmlDoc.createElement("ns:Residential")).Text = "true"
                    End If
                End With
            End With
            With .appendChild(xmlDoc.createElement("ns:ShippingChargesPayment"))
                .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "SENDER"
                With .appendChild(xmlDoc.createElement("ns:Payor"))
                    .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
                    .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                End With
            End With
            ' Always on because we will (at least) have sender email
            With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))
                If lngShipDate > 0 Then
                If IsDate(mobjWorksheet.Cells(lngRow, lngShipDate)) Then
                If CDate(mobjWorksheet.Cells(lngRow, lngShipDate)) > Date Then
                    .appendChild(xmlDoc.createElement("ns:SpecialServiceTypes")).Text = "FUTURE_DAY_SHIPMENT"
                End If
                End If
                End If
            
                If SheetColumn("Options") > 0 Then
                    If 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn("Options"))), "saturday") Then
                        .appendChild(xmlDoc.createElement("ns:SpecialServiceTypes")).Text = "SATURDAY_DELIVERY"
                    Else
                        .appendChild(xmlDoc.createElement("ns:SpecialServiceTypes")).Text = "WEEKDAY_DELIVERY"
                    End If
                End If
            End With
            
            .appendChild(xmlDoc.createElement("ns:RateRequestTypes")).Text = "ACCOUNT"
            ' MPS
            If lngPackageCount > 0 Then
                If IsNumeric(mobjWorksheet.Cells(lngRow + lngPackage, lngPackageCount)) Then
                    If CLng(mobjWorksheet.Cells(lngRow + lngPackage, lngPackageCount)) > 0 Then
                        .appendChild(xmlDoc.createElement("ns:PackageCount")).Text = CLng(mobjWorksheet.Cells(lngRow, lngPackageCount))
                    Else
                        .appendChild(xmlDoc.createElement("ns:PackageCount")).Text = "1"
                    End If
                Else
                    .appendChild(xmlDoc.createElement("ns:PackageCount")).Text = "1"
                End If
            Else
                .appendChild(xmlDoc.createElement("ns:PackageCount")).Text = "1"
            End If
            .appendChild(xmlDoc.createElement("ns:PackageDetail")).Text = "INDIVIDUAL_PACKAGES"
                        
            ' MPS
            lngPackage = 0
            Do
            
                With .appendChild(xmlDoc.createElement("ns:RequestedPackages"))
                
                    ' MPS
                    If lngPackageNumber > 0 Then
                    If IsNumeric(mobjWorksheet.Cells(lngRow + lngPackage, lngPackageNumber)) Then
                    If CLng(mobjWorksheet.Cells(lngRow + lngPackage, lngPackageNumber)) > 0 Then
                        .appendChild(xmlDoc.createElement("ns:SequenceNumber")).Text = CLng(mobjWorksheet.Cells(lngRow + lngPackage, lngPackageNumber))
                    End If
                    End If
                    End If
                
                    If SheetColumnExists("DeclaredValue") Then
                    If IsNumeric(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("DeclaredValue"))) Then
                    If CLng(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("DeclaredValue"))) > 0 Then
                        With .appendChild(xmlDoc.createElement("ns:InsuredValue"))
                            .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                            .appendChild(xmlDoc.createElement("ns:Amount")).Text = CDbl(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("DeclaredValue")))
                        End With
                    End If
                    End If
                    End If
                    
                    With .appendChild(xmlDoc.createElement("ns:Weight"))
                        .appendChild(xmlDoc.createElement("ns:Units")).Text = g_objSettings.Setting("WEIGHT_TYPE")
                        If SheetColumnExists("Weight") Then
                            If IsNumeric(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("Weight"))) Then
                                If CDbl(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("Weight"))) > 0 Then
                                    .appendChild(xmlDoc.createElement("ns:Value")).Text = CDbl(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("Weight")))
                                Else
                                    .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                                End If
                            Else
                                .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                            End If
                        Else
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                        End If
                    End With
                
                    If SheetColumnExists("Length") Then
                    If IsNumeric(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("Length"))) Then
                    If CLng(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("Length"))) > 0 Then
                        With .appendChild(xmlDoc.createElement("ns:Dimensions"))
                            .appendChild(xmlDoc.createElement("ns:Length")).Text = CDbl(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("Length")))
                            .appendChild(xmlDoc.createElement("ns:Width")).Text = CDbl(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("Width")))
                            .appendChild(xmlDoc.createElement("ns:Height")).Text = CDbl(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("Height")))
                            .appendChild(xmlDoc.createElement("ns:Units")).Text = IIf(Left(g_objSettings.Setting("WEIGHT_TYPE"), 2) = "LB", "IN", "CM")
                        End With
                    End If
                    End If
                    End If
                
    
                    If SheetColumnExists("DIMS") Then
                    If mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("DIMS")) <> "" Then
                    If UBound(Split(LCase(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("DIMS"))), "x")) >= 2 Then
                        With .appendChild(xmlDoc.createElement("ns:Dimensions"))
                            .appendChild(xmlDoc.createElement("ns:Length")).Text = CLng(Split(LCase(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("DIMS"))), "x")(0))
                            .appendChild(xmlDoc.createElement("ns:Width")).Text = CLng(Split(LCase(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("DIMS"))), "x")(1))
                            .appendChild(xmlDoc.createElement("ns:Height")).Text = CLng(Split(LCase(mobjWorksheet.Cells(lngRow + lngPackage, SheetColumn("DIMS"))), "x")(2))
                            .appendChild(xmlDoc.createElement("ns:Units")).Text = IIf(Left(g_objSettings.Setting("WEIGHT_TYPE"), 2) = "LB", "IN", "CM")
                        End With
                    End If
                    End If
                    End If
                    If 0 <> InStr(1, ServiceType(lngRow + lngPackage, strCountry), "GROUND") _
                    Or 0 <> InStr(1, ServiceType(lngRow + lngPackage, strCountry), "HOME") Then
                        If OptionTest("direct", "sign") Then
                            With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))
                                ' Hard code special
                                With .appendChild(xmlDoc.createElement("ns:SignatureOptionDetail"))
                                    .appendChild(xmlDoc.createElement("ns:OptionType")).Text = "DIRECT"
                                End With
                            End With
                        End If
                    Else ' Express
                        If OptionTest("adult", "sign") Then
                            With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))
                                ' Hard code special
                                With .appendChild(xmlDoc.createElement("ns:SignatureOptionDetail"))
                                    .appendChild(xmlDoc.createElement("ns:OptionType")).Text = "ADULT"
                                End With
                            End With
                        ElseIf OptionTest("indirect", "sign") Then
                            With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))
                                ' Hard code special
                                With .appendChild(xmlDoc.createElement("ns:SignatureOptionDetail"))
                                    .appendChild(xmlDoc.createElement("ns:OptionType")).Text = "INDIRECT"
                                End With
                            End With
                        ElseIf OptionTest("no", "sign") Then
                            With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))
                                ' Hard code special
                                With .appendChild(xmlDoc.createElement("ns:SignatureOptionDetail"))
                                    .appendChild(xmlDoc.createElement("ns:OptionType")).Text = "NO_SIGNATURE_REQUIRED"
                                End With
                            End With
                        End If
                    End If ' Ground/Express options
            
            
        
            
                End With ' RequestedPackages
                
                ' MPS
                boolShipIt = False
                If lngPackageCount <= 0 Then
                    boolShipIt = True
                ElseIf lngPackageNumber <= 0 Then
                    boolShipIt = True
                ElseIf Not IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageCount)) Then
                    boolShipIt = True
                ElseIf CLng(mobjWorksheet.Cells(lngRow, lngPackageCount)) <= 1 Then
                    boolShipIt = True
                ' Next package count
                ElseIf Not IsEmpty(mobjWorksheet.Cells(lngRow + lngPackage + 1, lngPackageCount)) _
                And IsNumeric(mobjWorksheet.Cells(lngRow + lngPackage + 1, lngPackageCount)) Then
                    boolShipIt = True
                ' package count
                ElseIf CLng(mobjWorksheet.Cells(lngRow, lngPackageCount)) <= lngPackage + 1 Then
                    boolShipIt = True
                ' next package number
                ElseIf Not IsNumeric(mobjWorksheet.Cells(lngRow + lngPackage + 1, lngPackageNumber)) Then
                    boolShipIt = True
                End If
                    
                If boolShipIt Then Exit Do
                lngPackage = lngPackage + 1
                
            Loop ' MPS
            
        End With ' RequestedShipment
    End With ' RateRequest

    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & lngRow & " RateRequest.xml", True)
        .Write xmlDoc.FirstChild.XML
        .Close
    End With

    g_strConfigHostEnvironment = g_objSettings.Setting("API_ENVIRONMENT")
    strTrans = SendRequestToGateway(xmlDoc.FirstChild.XML)
    
    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & lngRow & " RateReply.xml", True)
        .Write strTrans
        .Close
    End With
    Set xmlReply = New DOMDocument
    xmlReply.loadXML strTrans

    mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))).Font.ColorIndex = 1

    If xmlReply.selectSingleNode("v5:RateReply") Is Nothing Then
        mobjWorksheet.Cells(lngRow, SheetColumn("RateDetail")) = "Unknown reply:  " & strTrans
        mobjWorksheet.Cells(lngRow, SheetColumn("RateDetail")).Font.ColorIndex = 3
    End If

    For Each xmlnode1 In xmlReply.selectNodes("v5:RateReply")
        For Each xmlNode2 In xmlnode1.selectNodes("v5:Notifications")
            If UCase(xmlNode2.selectSingleNode("v5:Severity").Text) <> "SUCCESS" Then
                For Each xmlNode3 In xmlNode2.selectNodes("v5:LocalizedMessage")
                    If 0 = InStr(1, xmlNode3.Text, "is not valid for carriers") Then
                        If mobjWorksheet.Cells(lngRow, SheetColumn("RateDetail")) = "" Then
                            mobjWorksheet.Cells(lngRow, SheetColumn("RateDetail")) = "Errors or warnings:  "
                        End If
                        mobjWorksheet.Cells(lngRow, SheetColumn("RateDetail")) = mobjWorksheet.Cells(lngRow, SheetColumn("RateDetail")) _
                            & vbLf & xmlNode3.Text
                        mobjWorksheet.Cells(lngRow, SheetColumn("RateDetail")).Font.ColorIndex = 3
                    End If
                Next
            End If
        Next
    Next

    ' 2009-10-20, JFW, Rate for all service types.
    For Each xmlnode1 In xmlReply.selectNodes("v5:RateReply")
        For Each xmlNode2 In xmlnode1.selectNodes("v5:RateReplyDetails")
        
            If g_objSettings.Setting("RATE_TRANSIT_TIME") <> "" Then
            If CBool(g_objSettings.Setting("RATE_TRANSIT_TIME")) Then
                ' Ground
                For Each xmlNode3 In xmlNode2.selectNodes("v5:CommitDetails")
                    For Each xmlNode4 In xmlNode2.selectNodes("v5:TransitTime")
                        mobjWorksheet.Cells(lngRow, SheetColumn("TransitDetail")) = xmlNode4.Text
                    Next
                Next
                ' Express
                For Each xmlNode3 In xmlNode2.selectNodes("v5:DeliveryDayOfWeek")
                    mobjWorksheet.Cells(lngRow, SheetColumn("TransitDetail")) = xmlNode3.Text
                    For Each xmlNode4 In xmlNode2.selectNodes("v5:DeliveryTimestamp")
                        mobjWorksheet.Cells(lngRow, SheetColumn("TransitDetail")) = _
                            mobjWorksheet.Cells(lngRow, SheetColumn("TransitDetail")) & ", " _
                            & xmlNode4.Text
                    Next
                Next
                
                For Each xmlNode3 In xmlNode2.selectNodes("v5:DestinationAirportId")
                    mobjWorksheet.Cells(lngRow, SheetColumn("DestinationAirportId")) = xmlNode3.Text
                Next
                
                For Each xmlNode3 In xmlNode2.selectNodes("v5:DeliveryStation")
                    mobjWorksheet.Cells(lngRow, SheetColumn("DeliveryStation")) = xmlNode3.Text
                Next
                
            End If
            End If
            
            For Each xmlNode3 In xmlNode2.selectNodes("v5:RatedShipmentDetails")
                For Each xmlNode4 In xmlNode3.selectNodes("v5:ShipmentRateDetail")
                    If xmlNode2.selectSingleNode("v5:ActualRateType").Text = xmlNode4.selectSingleNode("v5:RateType").Text Then
                        For Each xmlNode5 In xmlNode4.selectNodes("v5:TotalNetCharge")
                            For Each xmlNode6 In xmlNode5.selectNodes("v5:Amount")
                                mobjWorksheet.Cells(lngRow, SheetColumn("NetCharge")) = xmlNode6.Text
                                mobjWorksheet.Cells(lngRow, SheetColumn("NetCharge")).NumberFormat = FORMAT_AMOUNT
                            Next
                        Next
                    
                        If g_objSettings.Setting("RATE_DETAIL") <> "" Then
                        If CBool(g_objSettings.Setting("RATE_DETAIL")) Then
                            mobjWorksheet.Cells(lngRow, SheetColumn("RateDetail")) = ""
                            For Each xmlNode5 In xmlNode4.selectNodes("*")
                                ' Get total content
                                If 0 <> InStr(1, xmlNode5.nodeName, "v5:Total") Then
                                ' Exclude some totals
                                If 0 = InStr(1, xmlNode5.nodeName, "v5:TotalRebates") Then
                                    If Not xmlNode5.selectSingleNode("v5:Amount") Is Nothing Then
                                        mobjWorksheet.Cells(lngRow, SheetColumn(Replace(xmlNode5.nodeName, "v5:", ""))) = xmlNode5.selectSingleNode("v5:Amount").Text
                                        mobjWorksheet.Cells(lngRow, SheetColumn(Replace(xmlNode5.nodeName, "v5:", ""))).NumberFormat = FORMAT_AMOUNT
                                    End If
                                End If
                                End If
                            
                                If 0 <> InStr(1, xmlNode5.nodeName, "v5:Surcharges") Then
                                    If Not xmlNode5.selectSingleNode("v5:SurchargeType") Is Nothing Then
                                    If Not xmlNode5.selectSingleNode("v5:Amount") Is Nothing Then
                                    If Not xmlNode5.selectSingleNode("v5:Amount").selectSingleNode("v5:Amount") Is Nothing Then
                                        mobjWorksheet.Cells(lngRow, SheetColumn("Surcharge " & xmlNode5.selectSingleNode("v5:SurchargeType").Text)) = _
                                            xmlNode5.selectSingleNode("v5:Amount").selectSingleNode("v5:Amount").Text
                                        mobjWorksheet.Cells(lngRow, SheetColumn("Surcharge " & xmlNode5.selectSingleNode("v5:SurchargeType").Text)).NumberFormat = FORMAT_AMOUNT
                                    End If
                                    End If
                                    End If
                                End If
                            
                                If 0 <> InStr(1, xmlNode5.nodeName, "v5:Taxes") Then
                                    If Not xmlNode5.selectSingleNode("v5:TaxType") Is Nothing Then
                                    If Not xmlNode5.selectSingleNode("v5:Amount") Is Nothing Then
                                    If Not xmlNode5.selectSingleNode("v5:Amount").selectSingleNode("v5:Amount") Is Nothing Then
                                        mobjWorksheet.Cells(lngRow, SheetColumn("Tax " & xmlNode5.selectSingleNode("v5:TaxType").Text)) = _
                                            xmlNode5.selectSingleNode("v5:Amount").selectSingleNode("v5:Amount").Text
                                        mobjWorksheet.Cells(lngRow, SheetColumn("Tax " & xmlNode5.selectSingleNode("v5:TaxType").Text)).NumberFormat = FORMAT_AMOUNT
                                    End If
                                    End If
                                    End If
                                End If
                              
                            Next
                        End If
                        End If
                        
                    End If
                Next
            Next
        Next
    Next
        
    ' MPS
    lngRow = lngRow + lngPackage
    
    Exit Sub

ErrorExit:
    mobjWorksheet.Cells(lngRow, SheetColumn("RateDetail")) = Err.Source & " - " & Err.Description
    If False Then
        Resume
    End If

End Sub


Public Sub APIBatchRateAvail()

    Dim objWorkbook As Workbook
    Dim i As Long
    
    mblnStop = False
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    Set objWorkbook = ActiveWorkbook
    Set mobjWorksheet = ActiveSheet
    
'    For Each objWorkbook In Workbooks
    
'        For Each mobjWorksheet In objWorkbook.Sheets
            
            Call APIBatchMPS
            
            i = CLng(g_objSettings.Setting("HEADER_ROW")) + 1
            Debug.Print mobjWorksheet.Name
            Do
                If SheetColumn(g_objSettings.Setting("SHEET_CONTACT")) < 1 Then
                    Exit Do
                End If
                If mobjWorksheet.Cells(i, SheetColumn(g_objSettings.Setting("SHEET_CONTACT"))) = "" Then Exit Do
                If mobjWorksheet.Cells(i, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = "" Then
                    ' Debug.Print mobjWorksheet.Cells(i, 1)
                    Call APIBatchRateAvailOne(i)
                End If
                i = i + 1
                DoEvents
                If mblnStop Then Exit Do
            Loop
            
'            Exit For
'        Next mobjWorksheet
        
        ' Exit For
'    Next objWorkbook
    
End Sub


Public Sub APIBatchRateAvailOne(ByRef lngRow As Long)

    On Error GoTo ErrorExit
    
    Dim obj As Object
    Dim xmlnode1 As IXMLDOMNode
    Dim xmlNode2 As IXMLDOMNode
    Dim xmlNode3 As IXMLDOMNode
    Dim xmlNode4 As IXMLDOMNode
    Dim xmlNode5 As IXMLDOMNode
    Dim xmlNode6 As IXMLDOMNode
    Dim xmlNode7 As IXMLDOMNode
    
    Dim strTrans As String
    Dim j As Long
    Dim strWeight As String
    Dim boolRateDone As Boolean
    Dim strActualRateType As String
    Dim strComment As String
    Dim objComment As Comment
    
    Dim lngPackage As Long
    Dim boolShipIt As Boolean
    
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject

    If SheetColumn("Options", True) > 0 Then
        strOption = OptionSplit(mobjWorksheet.Cells(lngRow, SheetColumn("Options")))
    Else
        ReDim strOption(0)
    End If

    Set xmlDoc = New DOMDocument

    With xmlDoc.appendChild(xmlDoc.createElement("ns:RateRequest"))
        Set obj = xmlDoc.createAttribute("xmlns:ns")
        obj.Text = "http://fedex.com/ws/rate/v5"
        Call .Attributes.setNamedItem(obj)
        
        Set obj = xmlDoc.createAttribute("xmlns:xsi")
        obj.Text = "http://www.w3.org/2001/XMLSchema-instance"
        Call .Attributes.setNamedItem(obj)
        
'        Set obj = xmlDoc.createAttribute("xsi:schemaLocation")
'        obj.Text = "http://fedex.com/ws/rate/v5/RateService_v5.xsd"
'        Call .Attributes.setNamedItem(obj)
        
        With .appendChild(xmlDoc.createElement("ns:WebAuthenticationDetail"))
            With .appendChild(xmlDoc.createElement("ns:UserCredential"))
                .appendChild(xmlDoc.createElement("ns:Key")).Text = g_objSettings.Setting("WS_KEY")
                If g_objSettings.Setting("WS_PASSWORD") <> "" Then
                    .appendChild(xmlDoc.createElement("ns:Password")).Text = g_objSettings.Setting("WS_PASSWORD")
                End If
            End With
        End With
        With .appendChild(xmlDoc.createElement("ns:ClientDetail"))
            .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
            .appendChild(xmlDoc.createElement("ns:MeterNumber")).Text = g_objSettings.Setting("METER_NUMBER")
        End With
        With .appendChild(xmlDoc.createElement("ns:TransactionDetail"))
            .appendChild(xmlDoc.createElement("ns:CustomerTransactionId")).Text = lngRow
        End With
        With .appendChild(xmlDoc.createElement("ns:Version"))
            .appendChild(xmlDoc.createElement("ns:ServiceId")).Text = "crs"
            .appendChild(xmlDoc.createElement("ns:Major")).Text = "5"
            .appendChild(xmlDoc.createElement("ns:Intermediate")).Text = "0"
            .appendChild(xmlDoc.createElement("ns:Minor")).Text = "0"
        End With
        
        '  <ReturnTransitAndCommit>true</ReturnTransitAndCommit>
        .appendChild(xmlDoc.createElement("ns:ReturnTransitAndCommit")).Text = "true"

        strCountry = CountryConv(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_COUNTRYCODE"))))
        With .appendChild(xmlDoc.createElement("ns:RequestedShipment"))
           If lngShipDate > 0 Then
                If IsDate(mobjWorksheet.Cells(lngRow, lngShipDate)) Then
                    .appendChild(xmlDoc.createElement("ns:ShipTimestamp")).Text = Format(CDate(mobjWorksheet.Cells(lngRow, lngShipDate)), "yyyy-mm-dd") & "T" & Format(Now, "HH:nn:ss") & "-05:00"
                End If
            Else
                .appendChild(xmlDoc.createElement("ns:ShipTimestamp")).Text = Format(Date, "yyyy-mm-dd") & "T" & Format(Now, "HH:nn:ss") & "-05:00"
            End If
            
            ' .appendChild(xmlDoc.createElement("ns:ServiceType")).Text = ServiceType(lngRow, strCountry)
            .appendChild(xmlDoc.createElement("ns:PackagingType")).Text = PackageType(lngRow)
            
            With .appendChild(xmlDoc.createElement("ns:TotalWeight"))
                .appendChild(xmlDoc.createElement("ns:Units")).Text = g_objSettings.Setting("WEIGHT_TYPE")
                If SheetColumnExists("TotalWgt") Then
                    If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt"))) Then
                        If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt"))) > 0 Then
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt")))
                        Else
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                        End If
                    Else
                        .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                    End If
                ElseIf SheetColumnExists("Weight") Then
                    If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) Then
                        If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) > 0 Then
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight")))
                        Else
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                        End If
                    Else
                        .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                    End If
                Else
                    .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                End If
            End With
            With .appendChild(xmlDoc.createElement("ns:Shipper"))
                With .appendChild(xmlDoc.createElement("ns:Contact"))
                    .appendChild(xmlDoc.createElement("ns:PersonName")).Text = g_objSettings.Setting("SENDER_CONTACT")
                    .appendChild(xmlDoc.createElement("ns:CompanyName")).Text = g_objSettings.Setting("SENDER_COMPANY")
                    .appendChild(xmlDoc.createElement("ns:PhoneNumber")).Text = g_objSettings.Setting("SENDER_PHONE")
                End With
                With .appendChild(xmlDoc.createElement("ns:Address"))
                    .appendChild(xmlDoc.createElement("ns:StreetLines")).Text = g_objSettings.Setting("SENDER_ADDR1")
                    .appendChild(xmlDoc.createElement("ns:City")).Text = g_objSettings.Setting("SENDER_CITY")
                    .appendChild(xmlDoc.createElement("ns:StateOrProvinceCode")).Text = g_objSettings.Setting("SENDER_STPV")
                    .appendChild(xmlDoc.createElement("ns:PostalCode")).Text = g_objSettings.Setting("SENDER_ZIP")
                    .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                End With
            End With
            With .appendChild(xmlDoc.createElement("ns:Recipient"))
                With .appendChild(xmlDoc.createElement("ns:Contact"))
                    '.appendChild(xmlDoc.createElement("ns:PersonName")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_CONTACT")))
                    '.appendChild(xmlDoc.createElement("ns:CompanyName")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_COMPANY")))
                    '.appendChild(xmlDoc.createElement("ns:PhoneNumber")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_PHONE")))
                End With
                With .appendChild(xmlDoc.createElement("ns:Address"))
                    '.appendChild(xmlDoc.createElement("ns:StreetLines")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS1")))
                    '.appendChild(xmlDoc.createElement("ns:City")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_CITY")))
                    '.appendChild(xmlDoc.createElement("ns:StateOrProvinceCode")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_STATE")))
                    .appendChild(xmlDoc.createElement("ns:PostalCode")).Text = ZipConv( _
                                    strCountry, _
                                    mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ZIP"))))
                    .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = strCountry
                End With
            End With
            With .appendChild(xmlDoc.createElement("ns:ShippingChargesPayment"))
                .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "SENDER"
                With .appendChild(xmlDoc.createElement("ns:Payor"))
                    .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
                    .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                End With
            End With
            ' Always on because we will (at least) have sender email
            With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))
                If lngShipDate > 0 Then
                If IsDate(mobjWorksheet.Cells(lngRow, lngShipDate)) Then
                If CDate(mobjWorksheet.Cells(lngRow, lngShipDate)) > Date Then
                    .appendChild(xmlDoc.createElement("ns:SpecialServiceTypes")).Text = "FUTURE_DAY_SHIPMENT"
                End If
                End If
                End If
            
                If SheetColumn("Options") > 0 Then
                    If 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn("Options"))), "saturday") Then
                        .appendChild(xmlDoc.createElement("ns:SpecialServiceTypes")).Text = "SATURDAY_DELIVERY"
                    Else
                        .appendChild(xmlDoc.createElement("ns:SpecialServiceTypes")).Text = "WEEKDAY_DELIVERY"
                    End If
                End If
            End With
            
            .appendChild(xmlDoc.createElement("ns:RateRequestTypes")).Text = "ACCOUNT"
            ' MPS
            If lngPackageCount > 0 Then
                If IsNumeric(mobjWorksheet.Cells(lngRow + lngPackage, lngPackageCount)) Then
                    If CLng(mobjWorksheet.Cells(lngRow + lngPackage, lngPackageCount)) > 0 Then
                        .appendChild(xmlDoc.createElement("ns:PackageCount")).Text = CLng(mobjWorksheet.Cells(lngRow, lngPackageCount))
                    Else
                        .appendChild(xmlDoc.createElement("ns:PackageCount")).Text = "1"
                    End If
                Else
                    .appendChild(xmlDoc.createElement("ns:PackageCount")).Text = "1"
                End If
            Else
                .appendChild(xmlDoc.createElement("ns:PackageCount")).Text = "1"
            End If
            .appendChild(xmlDoc.createElement("ns:PackageDetail")).Text = "INDIVIDUAL_PACKAGES"
            
            ' MPS
            lngPackage = 0
            Do
                With .appendChild(xmlDoc.createElement("ns:RequestedPackages"))
                
                    ' MPS
                    If lngPackageNumber > 0 Then
                    If IsNumeric(mobjWorksheet.Cells(lngRow + lngPackage, lngPackageNumber)) Then
                    If CLng(mobjWorksheet.Cells(lngRow + lngPackage, lngPackageNumber)) > 0 Then
                        .appendChild(xmlDoc.createElement("ns:SequenceNumber")).Text = CLng(mobjWorksheet.Cells(lngRow + lngPackage, lngPackageNumber))
                    End If
                    End If
                    End If
                
                    With .appendChild(xmlDoc.createElement("ns:Weight"))
                        .appendChild(xmlDoc.createElement("ns:Units")).Text = g_objSettings.Setting("WEIGHT_TYPE")
                        If SheetColumnExists("Weight") Then
                            If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) Then
                                If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) Then
                                    If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) > 0 Then
                                        .appendChild(xmlDoc.createElement("ns:Value")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight")))
                                    Else
                                        .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                                    End If
                                Else
                                    .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                                End If
                            Else
                                .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                            End If
                        Else
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                        End If
                    End With
                                    
                    If SheetColumnExists("Length") Then
                    If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Length"))) Then
                    If CLng(mobjWorksheet.Cells(lngRow, SheetColumn("Length"))) > 0 Then
                        With .appendChild(xmlDoc.createElement("ns:Dimensions"))
                            .appendChild(xmlDoc.createElement("ns:Length")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Length")))
                            .appendChild(xmlDoc.createElement("ns:Width")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Width")))
                            .appendChild(xmlDoc.createElement("ns:Height")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Height")))
                            .appendChild(xmlDoc.createElement("ns:Units")).Text = IIf(Left(g_objSettings.Setting("WEIGHT_TYPE"), 2) = "LB", "IN", "CM")
                        End With
                    End If
                    End If
                    End If
    
                    If SheetColumnExists("DIMS") Then
                    If mobjWorksheet.Cells(lngRow, SheetColumn("DIMS")) <> "" Then
                    If UBound(Split(LCase(mobjWorksheet.Cells(lngRow, SheetColumn("DIMS"))), "x")) >= 2 Then
                        With .appendChild(xmlDoc.createElement("ns:Dimensions"))
                            .appendChild(xmlDoc.createElement("ns:Length")).Text = CLng(Split(LCase(mobjWorksheet.Cells(lngRow, SheetColumn("DIMS"))), "x")(0))
                            .appendChild(xmlDoc.createElement("ns:Width")).Text = CLng(Split(LCase(mobjWorksheet.Cells(lngRow, SheetColumn("DIMS"))), "x")(1))
                            .appendChild(xmlDoc.createElement("ns:Height")).Text = CLng(Split(LCase(mobjWorksheet.Cells(lngRow, SheetColumn("DIMS"))), "x")(2))
                            .appendChild(xmlDoc.createElement("ns:Units")).Text = IIf(Left(g_objSettings.Setting("WEIGHT_TYPE"), 2) = "LB", "IN", "CM")
                        End With
                    End If
                    End If
                    End If
    
                End With ' RequestedPackages
                
                ' MPS
                boolShipIt = False
                If lngPackageCount <= 0 Then
                    boolShipIt = True
                ElseIf lngPackageNumber <= 0 Then
                    boolShipIt = True
                ElseIf Not IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageCount)) Then
                    boolShipIt = True
                ElseIf CLng(mobjWorksheet.Cells(lngRow, lngPackageCount)) <= 1 Then
                    boolShipIt = True
                ' Next package count
                ElseIf Not IsEmpty(mobjWorksheet.Cells(lngRow + lngPackage + 1, lngPackageCount)) _
                And IsNumeric(mobjWorksheet.Cells(lngRow + lngPackage + 1, lngPackageCount)) Then
                    boolShipIt = True
                ' package count
                ElseIf CLng(mobjWorksheet.Cells(lngRow, lngPackageCount)) <= lngPackage + 1 Then
                    boolShipIt = True
                ' next package number
                ElseIf IsEmpty(mobjWorksheet.Cells(lngRow + lngPackage + 1, lngPackageNumber)) Then
                    boolShipIt = True
                ElseIf Not IsNumeric(mobjWorksheet.Cells(lngRow + lngPackage + 1, lngPackageNumber)) Then
                    boolShipIt = True
                End If
                    
                If boolShipIt Then Exit Do
                lngPackage = lngPackage + 1
                
            Loop ' MPS
        End With ' RequestedShipment
    End With ' RateAvailable

    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & lngRow & " RateRequest.xml", True)
        .Write xmlDoc.FirstChild.XML
        .Close
    End With

    g_strConfigHostEnvironment = g_objSettings.Setting("API_ENVIRONMENT")
    strTrans = SendRequestToGateway(xmlDoc.FirstChild.XML)
    
    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & lngRow & " RateReply.xml", True)
        .Write strTrans
        .Close
    End With
    Set xmlReply = New DOMDocument
    xmlReply.loadXML strTrans

    If xmlReply.selectSingleNode("v5:RateReply") Is Nothing Then
        mobjWorksheet.Cells(lngRow, SheetColumn("RateAvailableComment")) = "Unknown reply:  " & strTrans
        mobjWorksheet.Cells(lngRow, SheetColumn("RateAvailableComment")).Font.ColorIndex = 3
    End If

    For Each xmlnode1 In xmlReply.selectNodes("v5:RateReply")
        For Each xmlNode2 In xmlnode1.selectNodes("v5:Notifications")
            If UCase(xmlNode2.selectSingleNode("v5:Severity").Text) <> "SUCCESS" Then
                For Each xmlNode3 In xmlNode2.selectNodes("v5:LocalizedMessage")
                    If 0 <> InStr(1, xmlNode3.Text, "is not valid for carriers") Then
                    ElseIf 0 <> InStr(1, xmlNode3.Text, "Money Back Guarantee is not eligible") Then
                    Else
                        If mobjWorksheet.Cells(lngRow, SheetColumn("RateAvailableComment")) = "" Then
                            mobjWorksheet.Cells(lngRow, SheetColumn("RateAvailableComment")) = "Errors or warnings:  "
                        End If
                        mobjWorksheet.Cells(lngRow, SheetColumn("RateAvailableComment")) = mobjWorksheet.Cells(lngRow, SheetColumn("RateDetail")) _
                            & vbLf & xmlNode3.Text
                        mobjWorksheet.Cells(lngRow, SheetColumn("RateAvailableComment")).Font.ColorIndex = 3
                    End If
                Next
            End If
        Next
    Next

    For Each xmlnode1 In xmlReply.selectNodes("v5:RateReply")
        For Each xmlNode2 In xmlnode1.selectNodes("v5:RateReplyDetails")
            For Each xmlNode3 In xmlNode2.selectNodes("v5:ServiceType")
                mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), SheetColumn(xmlNode3.Text & vbLf & "Commitment")) = xmlNode3.Text & vbLf & "Commitment"
                For Each xmlNode4 In xmlNode2.selectNodes("v5:TransitTime")
                    mobjWorksheet.Cells(lngRow, SheetColumn(xmlNode3.Text & vbLf & "Commitment")) = xmlNode4.Text
                Next
                For Each xmlNode4 In xmlNode2.selectNodes("v5:DeliveryTimestamp")
                    mobjWorksheet.Cells(lngRow, SheetColumn(xmlNode3.Text & vbLf & "Commitment")) = xmlNode4.Text
                Next
                mobjWorksheet.Columns(SheetColumn(xmlNode3.Text & vbLf & "Commitment")).AutoFit
            
                boolRateDone = False
                strActualRateType = ""
                strComment = ""
                For Each xmlNode4 In xmlNode2.selectNodes("v5:ActualRateType")
                    strActualRateType = xmlNode4.Text
                Next
                mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), SheetColumn(xmlNode3.Text & vbLf & "Rate")) = xmlNode3.Text & vbLf & "Rate"
                For Each xmlNode4 In xmlNode2.selectNodes("v5:RatedShipmentDetails")
                    For Each xmlNode5 In xmlNode4.selectNodes("v5:ShipmentRateDetail")
                        ' ActualRateType is undefined, use this first rate
                        If strActualRateType = "" Then boolRateDone = True
                        ' If this is the actual rate structure, then use it
                        For Each xmlNode6 In xmlNode5.selectNodes("v5:RateType")
                            If strActualRateType = xmlNode6.Text Then
                                boolRateDone = True
                            End If
                        Next
                        If boolRateDone Then
                            For Each xmlNode6 In xmlNode5.selectNodes("v5:TotalNetFedExCharge")
                                mobjWorksheet.Cells(lngRow, SheetColumn(xmlNode3.Text & vbLf & "Rate")) = xmlNode6.selectSingleNode("v5:Amount").Text
                                mobjWorksheet.Cells(lngRow, SheetColumn(xmlNode3.Text & vbLf & "Rate")).ClearComments
                            Next
                            For Each xmlNode6 In xmlNode5.selectNodes("v5:TotalBaseCharge")
                                strComment = strComment & "TotalBaseCharge " & Format(Val(xmlNode6.selectSingleNode("v5:Amount").Text), "0.00") & vbLf
                            Next
                            For Each xmlNode6 In xmlNode5.selectNodes("v5:TotalFreightDiscounts")
                                strComment = strComment & "TotalFreightDiscounts " & Format(Val(xmlNode6.selectSingleNode("v5:Amount").Text), "0.00") & vbLf
                            Next
                            For Each xmlNode6 In xmlNode5.selectNodes("v5:TotalNetFreight")
                                strComment = strComment & "TotalNetFreight " & Format(Val(xmlNode6.selectSingleNode("v5:Amount").Text), "0.00") & vbLf
                            Next
                            For Each xmlNode6 In xmlNode5.selectNodes("v5:TotalSurcharges")
                                strComment = strComment & "TotalSurcharges " & Format(Val(xmlNode6.selectSingleNode("v5:Amount").Text), "0.00") & vbLf
                            Next
                            For Each xmlNode6 In xmlNode5.selectNodes("v5:TotalNetFedExCharge")
                                strComment = strComment & "TotalNetFedExCharge " & Format(Val(xmlNode6.selectSingleNode("v5:Amount").Text), "0.00") & vbLf
                            Next
                            For Each xmlNode6 In xmlNode5.selectNodes("v5:TotalTaxes")
                                strComment = strComment & "TotalTaxes " & Format(Val(xmlNode6.selectSingleNode("v5:Amount").Text), "0.00") & vbLf
                            Next
                            For Each xmlNode6 In xmlNode5.selectNodes("v5:TotalNetCharge")
                                strComment = strComment & "TotalNetCharge " & Format(Val(xmlNode6.selectSingleNode("v5:Amount").Text), "0.00") & vbLf
                            Next
                            If strComment <> "" Then
                                Set objComment = mobjWorksheet.Cells(lngRow, SheetColumn(xmlNode3.Text & vbLf & "Rate")).AddComment(strComment)
                                If Not objComment Is Nothing Then
                                    objComment.Shape.Height = objComment.Shape.Height * 2
                                    objComment.Shape.Width = objComment.Shape.Width * 2
                                End If
                            End If
                            Exit For
                        End If
                    Next
                    If boolRateDone Then Exit For
                Next
            
            Next ' ServiceType
        Next
    Next
        
    lngRow = lngRow + lngPackage
    
    Exit Sub

ErrorExit:
    mobjWorksheet.Cells(lngRow, SheetColumn("RateDetail")) = Err.Source & " - " & Err.Description
    If False Then
        Resume
    End If

End Sub


Public Sub APIBatchReturn()

    Dim objWorkbook As Workbook
    Dim lngRow As Long, j As Long
    Dim lngShipTotal As Long
    Dim lngShipTry As Long
    Dim strShipDate As String
    Dim boolShipIt As Boolean
    Dim boolProcess As Boolean
    
    mblnStop = False
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    Set objWorkbook = ActiveWorkbook
    Set mobjWorksheet = ActiveSheet
    
'    For Each objWorkbook In Workbooks
    
'        For Each mobjWorksheet In objWorkbook.Sheets
            
            Call APIBatchMPS
            
            lngRow = CLng(g_objSettings.Setting("HEADER_ROW")) + 1
            Debug.Print mobjWorksheet.Name
            ' 2010-07-27, ZP, Add form id column for MPS
            SheetColumn ("FormId")
            Do ' each row of the sheet
                If SheetColumn(g_objSettings.Setting("SHEET_CONTACT")) < 1 Then
                    Exit Do
                End If
                If mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_CONTACT"))) = "" Then
                    Exit Do
                End If
                If mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = "" Then
                    boolProcess = False
                    If Not boolShipDate Then
                        boolProcess = True
                    ElseIf IsDate(mobjWorksheet.Cells(lngRow, lngShipDate)) Then
                        If CDate(mobjWorksheet.Cells(lngRow, lngShipDate)) = mdtShipDate Then
                            boolProcess = True
                        End If
                    End If
                    If boolProcess Then

                        ' 2011-11-25, JFW, Blank notifications for attempted shipment
                        If SheetColumnExists("Notifications") Then
                            If mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) <> "" Then
                                mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = ""
                            End If
                        End If

                        boolShipIt = False
                        If lngPackageCount <= 0 Then
                            boolShipIt = True
                        ElseIf lngPackageNumber <= 0 Then
                            boolShipIt = True
                        ElseIf Not IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageCount)) Then
                            boolShipIt = True
                        ElseIf Not CLng(mobjWorksheet.Cells(lngRow, lngPackageCount)) = 1 Then
                            boolShipIt = True
                        ElseIf Not IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageNumber)) Then
                            boolShipIt = False
                        ElseIf IsNumeric(mobjWorksheet.Cells(lngRow + 1 - mobjWorksheet.Cells(lngRow, lngPackageNumber), SheetColumn(g_objSettings.Setting("TrackingNumber")))) Then
                            boolShipIt = True
                        Else
                            mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = "This row fails single/MPS testing."
                            mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")).Font.ColorIndex = 3
                            boolShipIt = False
                        End If
                        
                        If boolShipIt Then
                            lngShipTry = lngShipTry + 1
                            If APIBatchReturnOne(lngRow) Then
                                lngShipTotal = lngShipTotal + 1
                            End If
                        End If
                        
                    End If
                End If
                lngRow = lngRow + 1
                DoEvents
                If mblnStop Then Exit Do
            Loop ' each row of the sheet
            
            ' 2010-07-27, ZP, Remove form id
            DeleteColumn ("FormId")
            ' First sheet only
'            Exit For
            
'        Next mobjWorksheet
        
        ' Exit For
'    Next objWorkbook
    
    MsgBox "Batch complete." & vbCrLf _
        & lngShipTry & " records." & vbCrLf _
        & lngShipTotal & " shipments."
    Exit Sub
    
End Sub





Public Function APIBatchReturnOne(lngRow As Long) As Boolean

    On Error GoTo ErrorExit
    
    Dim intFile As Long
    Dim strInsert As String

    Dim i As Long
    Dim strCountry As String
    Dim strCI As String
    Dim strLabel As String
    Dim strReply As String
    Dim strMasterAwb, strFormId, strPackageCnt As String
    Dim boolEmail As Boolean
    Dim boolCommodity As Boolean
    Dim lngCommodity As Long
    Dim strCommodity() As String
    Dim strCommodityList() As String
    Dim dblCustomsValue As Double
    Dim dblCommodityWeight As Double
    Dim dblCommodityCount As Double
    Dim obj As Object
    Dim xmlnode1 As IXMLDOMNode
    Dim xmlNode2 As IXMLDOMNode
    Dim xmlNode3 As IXMLDOMNode
    Dim xmlNode4 As IXMLDOMNode
    Dim xmlNode5 As IXMLDOMNode
    Dim xmlNode6 As IXMLDOMNode
    Dim xmlNode7 As IXMLDOMNode
    
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    Set xmlDoc = New DOMDocument

    If SheetColumn("Options", True) > 0 Then
        strOption = OptionSplit(mobjWorksheet.Cells(lngRow, SheetColumn("Options")))
    Else
        ReDim strOption(0)
    End If

    With xmlDoc.appendChild(xmlDoc.createElement("ns:ProcessShipmentRequest"))
        Set obj = xmlDoc.createAttribute("xmlns:ns")
        obj.Text = "http://fedex.com/ws/ship/v10"
        Call .Attributes.setNamedItem(obj)
        
        Set obj = xmlDoc.createAttribute("xmlns:xsi")
        obj.Text = "http://www.w3.org/2001/XMLSchema-instance"
        Call .Attributes.setNamedItem(obj)
        
        Set obj = xmlDoc.createAttribute("xsi:schemaLocation")
        obj.Text = "http://fedex.com/ws/ship/v10/ShipService_v10.xsd"
        Call .Attributes.setNamedItem(obj)
        
        With .appendChild(xmlDoc.createElement("ns:WebAuthenticationDetail"))
            With .appendChild(xmlDoc.createElement("ns:UserCredential"))
                .appendChild(xmlDoc.createElement("ns:Key")).Text = g_objSettings.Setting("WS_KEY")
                If g_objSettings.Setting("WS_PASSWORD") <> "" Then
                    .appendChild(xmlDoc.createElement("ns:Password")).Text = g_objSettings.Setting("WS_PASSWORD")
                End If
            End With
        End With
        With .appendChild(xmlDoc.createElement("ns:ClientDetail"))
            .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
            .appendChild(xmlDoc.createElement("ns:MeterNumber")).Text = g_objSettings.Setting("METER_NUMBER")
        End With
        With .appendChild(xmlDoc.createElement("ns:TransactionDetail"))
            .appendChild(xmlDoc.createElement("ns:CustomerTransactionId")).Text = lngRow
        End With
        With .appendChild(xmlDoc.createElement("ns:Version"))
            .appendChild(xmlDoc.createElement("ns:ServiceId")).Text = "ship"
            .appendChild(xmlDoc.createElement("ns:Major")).Text = "10"
            .appendChild(xmlDoc.createElement("ns:Intermediate")).Text = "0"
            .appendChild(xmlDoc.createElement("ns:Minor")).Text = "0"
        End With
        strCountry = CountryConv(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_COUNTRYCODE"))))
        With .appendChild(xmlDoc.createElement("ns:RequestedShipment"))
            If lngShipDate > 0 Then
                If IsDate(mobjWorksheet.Cells(lngRow, lngShipDate)) Then
                    .appendChild(xmlDoc.createElement("ns:ShipTimestamp")).Text = Format(CDate(mobjWorksheet.Cells(lngRow, lngShipDate)), "yyyy-mm-dd") & "T" & Format(Now, "HH:nn:ss") & "-05:00"
                End If
            Else
                .appendChild(xmlDoc.createElement("ns:ShipTimestamp")).Text = Format(Date, "yyyy-mm-dd") & "T" & Format(Now, "HH:nn:ss") & "-05:00"
            End If
            
            .appendChild(xmlDoc.createElement("ns:DropoffType")).Text = "REGULAR_PICKUP"
            .appendChild(xmlDoc.createElement("ns:ServiceType")).Text = ServiceType(lngRow, strCountry)
            .appendChild(xmlDoc.createElement("ns:PackagingType")).Text = PackageType(lngRow)
                        
            With .appendChild(xmlDoc.createElement("ns:TotalWeight"))
                .appendChild(xmlDoc.createElement("ns:Units")).Text = g_objSettings.Setting("WEIGHT_TYPE")
                If SheetColumnExists("TotalWgt") Then
                    If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt"))) Then
                        If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt"))) > 0 Then
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt")))
                        Else
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                        End If
                    Else
                        .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                    End If
                ElseIf SheetColumnExists("Weight") Then
                    If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) Then
                        If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) > 0 Then
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight")))
                        Else
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                        End If
                    Else
                        .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                    End If
                Else
                    .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                End If
            End With
            With .appendChild(xmlDoc.createElement("ns:Shipper"))
                With .appendChild(xmlDoc.createElement("ns:Contact"))
                    .appendChild(xmlDoc.createElement("ns:PersonName")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_CONTACT")))
                    .appendChild(xmlDoc.createElement("ns:CompanyName")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_COMPANY")))
                    .appendChild(xmlDoc.createElement("ns:PhoneNumber")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_PHONE")))
                End With
                With .appendChild(xmlDoc.createElement("ns:Address"))
                    .appendChild(xmlDoc.createElement("ns:StreetLines")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS1")))
                    If g_objSettings.Setting("SHEET_ADDRESS2") <> "" Then
                        If mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS2"))) <> "" Then
                            .appendChild(xmlDoc.createElement("ns:StreetLines")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS2")))
                        End If
                    End If
                    .appendChild(xmlDoc.createElement("ns:City")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_CITY")))
                    .appendChild(xmlDoc.createElement("ns:StateOrProvinceCode")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_STATE")))
                    .appendChild(xmlDoc.createElement("ns:PostalCode")).Text = ZipConv( _
                                    strCountry, _
                                    mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ZIP"))))
                    .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = strCountry
                    If OptionTest("resi") Then
                        .appendChild(xmlDoc.createElement("ns:Residential")).Text = "true"
                    End If
                End With
            End With
            With .appendChild(xmlDoc.createElement("ns:Recipient"))
                With .appendChild(xmlDoc.createElement("ns:Contact"))
                    .appendChild(xmlDoc.createElement("ns:PersonName")).Text = g_objSettings.Setting("SENDER_CONTACT")
                    .appendChild(xmlDoc.createElement("ns:CompanyName")).Text = g_objSettings.Setting("SENDER_COMPANY")
                    .appendChild(xmlDoc.createElement("ns:PhoneNumber")).Text = g_objSettings.Setting("SENDER_PHONE")
                End With
                With .appendChild(xmlDoc.createElement("ns:Address"))
                    .appendChild(xmlDoc.createElement("ns:StreetLines")).Text = g_objSettings.Setting("SENDER_ADDR1")
                    .appendChild(xmlDoc.createElement("ns:City")).Text = g_objSettings.Setting("SENDER_CITY")
                    .appendChild(xmlDoc.createElement("ns:StateOrProvinceCode")).Text = g_objSettings.Setting("SENDER_STPV")
                    .appendChild(xmlDoc.createElement("ns:PostalCode")).Text = g_objSettings.Setting("SENDER_ZIP")
                    .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                End With
            End With
            With .appendChild(xmlDoc.createElement("ns:ShippingChargesPayment"))
                If OptionIndex(strOption, "Bill") >= 0 And OptionIndex(strOption, "Bill") = (OptionIndex(strOption, "Recip") - 1) Then
                    .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "RECIPIENT"
                    i = OptionIndex(strOption, "Recip") + 1
                ElseIf OptionIndex(strOption, "BillRecip") >= 0 Then
                    .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "RECIPIENT"
                    i = OptionIndex(strOption, "BillRecip") + 1
                ElseIf OptionIndex(strOption, "Bill") >= 0 And OptionIndex(strOption, "Bill") = (OptionIndex(strOption, "Third") - 1) Then
                    .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "THIRD_PARTY"
                    i = OptionIndex(strOption, "Third") + 1
                ElseIf OptionIndex(strOption, "BillThird") >= 0 Then
                    .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "THIRD_PARTY"
                    i = OptionIndex(strOption, "BillThird") + 1
                Else
                    .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "SENDER"
                    With .appendChild(xmlDoc.createElement("ns:Payor"))
                        .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
                        .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                    End With
                     i = -1
                End If
                If i >= 0 Then
                    If i <= UBound(strOption) Then
                        With .appendChild(xmlDoc.createElement("ns:Payor"))
                            .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = strOption(i)
                            If i + 1 <= UBound(strOption) Then
                                .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = strOption(i + 1)
                            Else
                                .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                            End If
                        End With
                    Else
                        With .appendChild(xmlDoc.createElement("ns:Payor"))
                            .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = "123456789"
                            .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                        End With
                    End If
                End If
            End With
            
            If 0 <> InStr(1, ServiceType(lngRow, strCountry), "FREIGHT") Then
                With .appendChild(xmlDoc.createElement("ns:ExpressFreightDetail"))
                    .appendChild(xmlDoc.createElement("ns:ShippersLoadAndCount")).Text = "1"
                    .appendChild(xmlDoc.createElement("ns:BookingConfirmationNumber")).Text = "123456"
                End With ' Special services
            End If
            
            ' 2012-10-19, JFW, Can't have Special on freight.
            If 0 = InStr(1, ServiceType(lngRow, strCountry), "FREIGHT") Then
            ' Always on because we will (at least) have sender email
            With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))

                If lngShipDate > 0 Then
                If IsDate(mobjWorksheet.Cells(lngRow, lngShipDate)) Then
                If CDate(mobjWorksheet.Cells(lngRow, lngShipDate)) > Date Then
                    .appendChild(xmlDoc.createElement("ns:SpecialServiceTypes")).Text = "FUTURE_DAY_SHIPMENT"
                End If
                End If
                End If
            
                If OptionIndex(strOption, "Saturday") >= 0 Then
                    .appendChild(xmlDoc.createElement("ns:SpecialServiceTypes")).Text = "SATURDAY_DELIVERY"
                Else
                    ' .appendChild(xmlDoc.createElement("ns:SpecialServiceTypes")).Text = "WEEKDAY_DELIVERY"
                End If
                
                ' ETD goes here
                If g_objSettings.Setting("ETD") <> "" Then
                If g_objSettings.Setting("ETD") Then
                If SheetColumn("Options") > 0 Then
                If 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn("Options"))), "etd") Then
                If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                    .appendChild(xmlDoc.createElement("ns:SpecialServiceTypes")).Text = "ELECTRONIC_TRADE_DOCUMENTS"
                    With .appendChild(xmlDoc.createElement("ns:EtdDetail"))
                        .appendChild(xmlDoc.createElement("ns:RequestedDocumentCopies")).Text = "COMMERCIAL_INVOICE"
                    End With
                End If
                End If
                End If
                End If
                End If
                
                boolEmail = False
                If g_objSettings.Setting("SENDER_EMAIL") <> "" Then
                    boolEmail = True
                End If
                If g_objSettings.Setting("SHEET_EMAIL") <> "" Then
                If SheetColumn(g_objSettings.Setting("SHEET_EMAIL")) > 0 Then
                If mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_EMAIL"))) <> "" Then
                    boolEmail = True
                End If
                End If
                End If
                    
                If boolEmail Then
                With .appendChild(xmlDoc.createElement("ns:EMailNotificationDetail"))
                    If g_objSettings.Setting("SENDER_EMAIL") <> "" Then
                        With .appendChild(xmlDoc.createElement("ns:Recipients"))
                            .appendChild(xmlDoc.createElement("ns:EMailNotificationRecipientType")).Text = "SHIPPER"
                            .appendChild(xmlDoc.createElement("ns:EMailAddress")).Text = g_objSettings.Setting("SENDER_EMAIL")
                            .appendChild(xmlDoc.createElement("ns:NotifyOnShipment")).Text = "true"
                            .appendChild(xmlDoc.createElement("ns:NotifyOnException")).Text = "true"
                            .appendChild(xmlDoc.createElement("ns:NotifyOnDelivery")).Text = "true"
                            .appendChild(xmlDoc.createElement("ns:Format")).Text = "HTML"
                            With .appendChild(xmlDoc.createElement("ns:Localization"))
                                .appendChild(xmlDoc.createElement("ns:LanguageCode")).Text = "EN"
                            End With
                        End With
                    End If
                    If g_objSettings.Setting("SHEET_EMAIL") <> "" Then
                    If SheetColumn(g_objSettings.Setting("SHEET_EMAIL")) > 0 Then
                    If mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_EMAIL"))) <> "" Then
                        With .appendChild(xmlDoc.createElement("ns:Recipients"))
                            .appendChild(xmlDoc.createElement("ns:EMailNotificationRecipientType")).Text = "RECIPIENT"
                            .appendChild(xmlDoc.createElement("ns:EMailAddress")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_EMAIL")))
                            .appendChild(xmlDoc.createElement("ns:NotifyOnShipment")).Text = "true"
                            .appendChild(xmlDoc.createElement("ns:NotifyOnException")).Text = "true"
                            .appendChild(xmlDoc.createElement("ns:NotifyOnDelivery")).Text = "true"
                            .appendChild(xmlDoc.createElement("ns:Format")).Text = "HTML"
                            With .appendChild(xmlDoc.createElement("ns:Localization"))
                                .appendChild(xmlDoc.createElement("ns:LanguageCode")).Text = "EN"
                            End With
                        End With
                    End If
                    End If
                    End If
                End With ' Email
                End If ' Email
                
            End With ' Special services
            End If
            ' temp
            
            If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                With .appendChild(xmlDoc.createElement("ns:CustomsClearanceDetail"))
                    
                    With .appendChild(xmlDoc.createElement("ns:DutiesPayment"))
                        If OptionIndex(strOption, "Duty") >= 0 And OptionIndex(strOption, "Duty") = (OptionIndex(strOption, "Consignee") - 1) Then
                            .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "RECIPIENT"
                            i = -1
                        ElseIf OptionIndex(strOption, "DutyConsignee") >= 0 Then
                            .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "RECIPIENT"
                            i = -1
                        ElseIf OptionIndex(strOption, "Duty") >= 0 And OptionIndex(strOption, "Duty") = (OptionIndex(strOption, "Recip") - 1) Then
                            .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "RECIPIENT"
                            i = OptionIndex(strOption, "Recip") + 1
                        ElseIf OptionIndex(strOption, "DutyRecip") >= 0 Then
                            .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "RECIPIENT"
                            i = OptionIndex(strOption, "DutyRecip") + 1
                        ElseIf OptionIndex(strOption, "Duty") >= 0 And OptionIndex(strOption, "Duty") = (OptionIndex(strOption, "Third") - 1) Then
                            .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "THIRD_PARTY"
                            i = OptionIndex(strOption, "Third") + 1
                        ElseIf OptionIndex(strOption, "DutyThird") >= 0 Then
                            .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "THIRD_PARTY"
                            i = OptionIndex(strOption, "DutyThird") + 1
                        Else
                            .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "SENDER"
                            With .appendChild(xmlDoc.createElement("ns:Payor"))
                                .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
                                .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                            End With
                             i = -1
                        End If
                        If i >= 0 Then
                            If i < UBound(strOption) Then
                                If IsNumeric(strOption(i)) Then
                                    With .appendChild(xmlDoc.createElement("ns:Payor"))
                                        .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = strOption(i)
                                        .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = strOption(i + 1) ' g_objSettings.Setting("SENDER_COUNTRY")
                                    End With
                                End If
'                            Else
'                                With .appendChild(xmlDoc.createElement("ns:Payor"))
'                                    .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = "123456789"
'                                    .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
'                                End With
                            End If
                        End If
                    End With
                    
                    boolCommodity = False
                    If SheetColumn("Commodity", True) > 0 Then
                        If mobjWorksheet.Cells(lngRow, SheetColumn("Commodity")) <> "" Then
                            boolCommodity = True
                        End If
                    End If
                    If boolCommodity Then
                        
                        dblCustomsValue = 0
                        strCommodityList = Split(mobjWorksheet.Cells(lngRow, SheetColumn("Commodity")), Chr(10))
                        For lngCommodity = LBound(strCommodityList) To UBound(strCommodityList)
                            strCommodity = Split(strCommodityList(lngCommodity), ";")
                            dblCustomsValue = dblCustomsValue + CDbl(Trim(strCommodity(3)))
                        Next lngCommodity
                        dblCommodityCount = UBound(strCommodityList) + 1
                        
                        
                        .appendChild(xmlDoc.createElement("ns:DocumentContent")).Text = "NON_DOCUMENTS"
                        With .appendChild(xmlDoc.createElement("ns:CustomsValue"))
                            .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                            .appendChild(xmlDoc.createElement("ns:Amount")).Text = Format(dblCustomsValue, "0.00")
                        End With
                        strCommodityList = Split(mobjWorksheet.Cells(lngRow, SheetColumn("Commodity")), Chr(10))
                        For lngCommodity = LBound(strCommodityList) To UBound(strCommodityList)
                            strCommodity = Split(strCommodityList(lngCommodity), ";")
'                            If LBound(strCommodity) < 0 _
'                            Or UBound(strCommodity) <> 5 Then
'                                Err.Raise 12345, "BatchShip", "Commodity error, line " & lngRow & ", """ & strCommodityList(lngCommodity) & """, must be ""Description;Country;Quantity;Value;HSCode;Weight""."
'                            End If
                            With .appendChild(xmlDoc.createElement("ns:Commodities"))
                                .appendChild(xmlDoc.createElement("ns:NumberOfPieces")).Text = "1"
                                .appendChild(xmlDoc.createElement("ns:Description")).Text = Trim(strCommodity(0))
                                                            
                                .appendChild(xmlDoc.createElement("ns:CountryOfManufacture")).Text = Trim(strCommodity(1))
                                
                                If UBound(strCommodity) >= 4 Then
                                If Trim(strCommodity(4)) <> "" Then
                                    .appendChild(xmlDoc.createElement("ns:HarmonizedCode")).Text = Trim(strCommodity(4))
                                End If
                                End If
                                
                                With .appendChild(xmlDoc.createElement("ns:Weight"))
                                    .appendChild(xmlDoc.createElement("ns:Units")).Text = "LB"
                                    ' .appendChild(xmlDoc.createElement("ns:Value")).Text = Trim(strCommodity(5))
                                    dblCommodityWeight = Val(g_objSettings.Setting("SHIPPING_WEIGHT"))
                                    If SheetColumnExists("Weight") Then
                                        If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) Then
                                            If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) > 0 Then
                                                dblCommodityWeight = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight")))
                                            End If
                                        End If
                                    End If
                                    .appendChild(xmlDoc.createElement("ns:Value")).Text = dblCommodityWeight / dblCommodityCount
                                End With
                                
                                .appendChild(xmlDoc.createElement("ns:Quantity")).Text = Trim(strCommodity(2))
                                .appendChild(xmlDoc.createElement("ns:QuantityUnits")).Text = "EA"
                                With .appendChild(xmlDoc.createElement("ns:UnitPrice"))
                                    .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                                    .appendChild(xmlDoc.createElement("ns:Amount")).Text = CDbl(Trim(strCommodity(3))) / CDbl(Trim(strCommodity(2)))
                                End With
                                With .appendChild(xmlDoc.createElement("ns:CustomsValue"))
                                    .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                                    .appendChild(xmlDoc.createElement("ns:Amount")).Text = Trim(strCommodity(3))
                                End With
                            End With
                        Next lngCommodity
                    Else
                        ' Documents only
                        .appendChild(xmlDoc.createElement("ns:DocumentContent")).Text = "DOCUMENTS_ONLY"
                        With .appendChild(xmlDoc.createElement("ns:CustomsValue"))
                            .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                            .appendChild(xmlDoc.createElement("ns:Amount")).Text = CountryCustomsValue(strCountry)
                        End With
                        With .appendChild(xmlDoc.createElement("ns:Commodities"))
                            .appendChild(xmlDoc.createElement("ns:NumberOfPieces")).Text = "1"
                            .appendChild(xmlDoc.createElement("ns:Description")).Text = "Business Correspondence envelope"
                            .appendChild(xmlDoc.createElement("ns:CountryOfManufacture")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                            With .appendChild(xmlDoc.createElement("ns:Weight"))
                                .appendChild(xmlDoc.createElement("ns:Units")).Text = "LB"
                                If SheetColumnExists("Weight") Then
                                    If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) Then
                                        If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) > 0 Then
                                            .appendChild(xmlDoc.createElement("ns:Value")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight")))
                                        Else
                                            .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                                        End If
                                    Else
                                        .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                                    End If
                                Else
                                    .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                                End If
                            End With
                            .appendChild(xmlDoc.createElement("ns:Quantity")).Text = "1"
                            .appendChild(xmlDoc.createElement("ns:QuantityUnits")).Text = "EA"
                            With .appendChild(xmlDoc.createElement("ns:UnitPrice"))
                                .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                                .appendChild(xmlDoc.createElement("ns:Amount")).Text = CountryCustomsValue(strCountry)
                            End With
                            With .appendChild(xmlDoc.createElement("ns:CustomsValue"))
                                .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                                .appendChild(xmlDoc.createElement("ns:Amount")).Text = CountryCustomsValue(strCountry)
                            End With
                        End With
                    End If ' Commodity
                    
                    If strCountry <> "US" Then
                        With .appendChild(xmlDoc.createElement("ns:ExportDetail"))
                            If dblCustomsValue >= 2000 Then
                                .appendChild(xmlDoc.createElement("ns:B13AFilingOption")).Text = "MANUALLY_ATTACHED"
                            Else
                                .appendChild(xmlDoc.createElement("ns:B13AFilingOption")).Text = "NOT_REQUIRED"
                            End If
                        End With
                    End If

                    If False And strCountry = "US" Then
                            If 0 <> InStr(1, LCase(PackageType(lngRow)), "env") Then
                                .appendChild(xmlDoc.createElement("ns:AdmissibilityPackageType")).Text = "ENV"
                            ElseIf 0 <> InStr(1, LCase(PackageType(lngRow)), "pak") Then
                                .appendChild(xmlDoc.createElement("ns:AdmissibilityPackageType")).Text = "BAG"
                            Else
                                .appendChild(xmlDoc.createElement("ns:AdmissibilityPackageType")).Text = "BOX"
                            End If
                    End If
                End With
            End If
                        
            With .appendChild(xmlDoc.createElement("ns:LabelSpecification"))
                .appendChild(xmlDoc.createElement("ns:LabelFormatType")).Text = g_objSettings.SettingWithDefault("LABEL_FORMAT_TYPE", "COMMON2D")
                .appendChild(xmlDoc.createElement("ns:ImageType")).Text = g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII")
                .appendChild(xmlDoc.createElement("ns:LabelStockType")).Text = g_objSettings.SettingWithDefault("LABEL_STOCK_TYPE", "STOCK_4X6.75_LEADING_DOC_TAB")
                If InStr(1, g_objSettings.SettingWithDefault("LABEL_STOCK_TYPE", "STOCK_4X6.75_LEADING_DOC_TAB"), "DOC_TAB") <> 0 Then
                    .appendChild(xmlDoc.createElement("ns:LabelPrintingOrientation")).Text = "TOP_EDGE_OF_TEXT_FIRST"
                    With .appendChild(xmlDoc.createElement("ns:CustomerSpecifiedDetail"))
                        With .appendChild(xmlDoc.createElement("ns:DocTabContent"))
                            .appendChild(xmlDoc.createElement("ns:DocTabContentType")).Text = "STANDARD"
                        End With
                    End With
                End If
            End With
            
            If g_objSettings.Setting("ETD") <> "" Then
            If g_objSettings.Setting("ETD") Then
            If SheetColumn("Options") > 0 Then
            If 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn("Options"))), "etd") Then
            If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                With .appendChild(xmlDoc.createElement("ns:ShippingDocumentSpecification"))
                    .appendChild(xmlDoc.createElement("ns:ShippingDocumentTypes")).Text = "COMMERCIAL_INVOICE"
                    With .appendChild(xmlDoc.createElement("ns:CommercialInvoiceDetail"))
                        With .appendChild(xmlDoc.createElement("ns:Format"))
                            .appendChild(xmlDoc.createElement("ns:ImageType")).Text = "PDF"
                            .appendChild(xmlDoc.createElement("ns:StockType")).Text = "PAPER_LETTER"
                        End With
                        If g_objSettings.Setting("CI_LETTERHEAD") <> "" Then
                            With .appendChild(xmlDoc.createElement("ns:CustomerImageUsages"))
                                .appendChild(xmlDoc.createElement("ns:Type")).Text = "LETTERHEAD"
                                .appendChild(xmlDoc.createElement("ns:Id")).Text = g_objSettings.Setting("CI_LETTERHEAD")
                            End With
                        End If
                        If g_objSettings.Setting("CI_SIGNATURE") <> "" Then
                            With .appendChild(xmlDoc.createElement("ns:CustomerImageUsages"))
                                .appendChild(xmlDoc.createElement("ns:Type")).Text = "SIGNATURE"
                                .appendChild(xmlDoc.createElement("ns:Id")).Text = g_objSettings.Setting("CI_SIGNATURE")
                            End With
                        End If
                    End With
                End With
            End If
            End If
            End If
            End If
            End If
                            
            .appendChild(xmlDoc.createElement("ns:RateRequestTypes")).Text = "ACCOUNT"

            strPackageCnt = ""
            If lngPackageNumber > 0 Then
            If IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageNumber)) Then
            If CLng(mobjWorksheet.Cells(lngRow, lngPackageNumber)) > 0 Then
                
                If CLng(mobjWorksheet.Cells(lngRow, lngPackageNumber)) > 1 Then
                    'MasterTrackingId is required for MPS child lines //zp new
                    strMasterAwb = mobjWorksheet.Cells(lngRow + 1 - mobjWorksheet.Cells(lngRow, _
                                    lngPackageNumber), SheetColumn(g_objSettings.Setting("TrackingNumber")))
                    strFormId = mobjWorksheet.Cells(lngRow + 1 - mobjWorksheet.Cells(lngRow, _
                                    lngPackageNumber), SheetColumn("FormId"))
                    If mobjWorksheet.Cells(lngRow, lngPackageCount) = "" And _
                            mobjWorksheet.Cells(lngRow, lngPackageNumber) <> "" And _
                            IsNumeric(strMasterAwb) Then
                        With .appendChild(xmlDoc.createElement("ns:MasterTrackingId"))
                            If strFormId <> "" Then
                                ' Express MPS
                                .appendChild(xmlDoc.createElement("ns:FormId")).Text = strFormId
                            End If
                            .appendChild(xmlDoc.createElement("ns:TrackingNumber")).Text = strMasterAwb
                        End With
                    End If
                End If
                
                'PackageCount is required for MPS master and child lines
                strPackageCnt = mobjWorksheet.Cells(lngRow + 1 - mobjWorksheet.Cells(lngRow, _
                                 lngPackageNumber), lngPackageCount)
                '//zp new end
            End If ' > 0
            End If ' numeric
            End If ' MPS_Id
            
            If IsNumeric(strPackageCnt) Then
                .appendChild(xmlDoc.createElement("ns:PackageCount")).Text = strPackageCnt
            Else
                .appendChild(xmlDoc.createElement("ns:PackageCount")).Text = "1"
            End If
            ' .appendChild(xmlDoc.createElement("ns:PackageDetail")).Text = "INDIVIDUAL_PACKAGES"
            
            With .appendChild(xmlDoc.createElement("ns:RequestedPackageLineItems"))
            
                'SequenceNumber is required for MPS //zp new
                If lngPackageNumber > 0 Then
                If mobjWorksheet.Cells(lngRow, lngPackageNumber) <> "" Then
                If IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageNumber)) Then
                If CLng(mobjWorksheet.Cells(lngRow, lngPackageNumber)) > 0 Then
                    .appendChild(xmlDoc.createElement("ns:SequenceNumber")).Text = mobjWorksheet.Cells(lngRow, lngPackageNumber)
                End If ' //zp new end
                End If
                End If
                End If
                
                
                If SheetColumnExists("DeclaredValue") Then
                If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("DeclaredValue"))) Then
                If CLng(mobjWorksheet.Cells(lngRow, SheetColumn("DeclaredValue"))) > 0 Then
                    With .appendChild(xmlDoc.createElement("ns:InsuredValue"))
                        .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                        .appendChild(xmlDoc.createElement("ns:Amount")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("DeclaredValue")))
                    End With
                End If
                End If
                End If
                
                With .appendChild(xmlDoc.createElement("ns:Weight"))
                    .appendChild(xmlDoc.createElement("ns:Units")).Text = g_objSettings.Setting("WEIGHT_TYPE")
                    If SheetColumnExists("Weight") Then
                        If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) Then
                            If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) > 0 Then
                                .appendChild(xmlDoc.createElement("ns:Value")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight")))
                            Else
                                .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                            End If
                        Else
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                        End If
                    Else
                        .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                    End If
                End With
                
                If SheetColumnExists("Length") Then
                If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Length"))) Then
                If CLng(mobjWorksheet.Cells(lngRow, SheetColumn("Length"))) > 0 Then
                    With .appendChild(xmlDoc.createElement("ns:Dimensions"))
                        .appendChild(xmlDoc.createElement("ns:Length")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Length")))
                        .appendChild(xmlDoc.createElement("ns:Width")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Width")))
                        .appendChild(xmlDoc.createElement("ns:Height")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Height")))
                        .appendChild(xmlDoc.createElement("ns:Units")).Text = IIf(Left(g_objSettings.Setting("WEIGHT_TYPE"), 2) = "LB", "IN", "CM")
                    End With
                End If
                End If
                End If
                
                If SheetColumnExists("DIMS") Then
                If mobjWorksheet.Cells(lngRow, SheetColumn("DIMS")) <> "" Then
                If UBound(Split(LCase(mobjWorksheet.Cells(lngRow, SheetColumn("DIMS"))), "x")) >= 2 Then
                    With .appendChild(xmlDoc.createElement("ns:Dimensions"))
                        .appendChild(xmlDoc.createElement("ns:Length")).Text = CLng(Split(LCase(mobjWorksheet.Cells(lngRow, SheetColumn("DIMS"))), "x")(0))
                        .appendChild(xmlDoc.createElement("ns:Width")).Text = CLng(Split(LCase(mobjWorksheet.Cells(lngRow, SheetColumn("DIMS"))), "x")(1))
                        .appendChild(xmlDoc.createElement("ns:Height")).Text = CLng(Split(LCase(mobjWorksheet.Cells(lngRow, SheetColumn("DIMS"))), "x")(2))
                        .appendChild(xmlDoc.createElement("ns:Units")).Text = IIf(Left(g_objSettings.Setting("WEIGHT_TYPE"), 2) = "LB", "IN", "CM")
                    End With
                End If
                End If
                End If
                
                If g_objSettings.Setting("SHEET_REFERENCE") <> "" Then
                    If SheetColumn(g_objSettings.Setting("SHEET_REFERENCE")) > 0 Then
                        With .appendChild(xmlDoc.createElement("ns:CustomerReferences"))
                            .appendChild(xmlDoc.createElement("ns:CustomerReferenceType")).Text = "CUSTOMER_REFERENCE"
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_REFERENCE")))
                        End With
                    End If
                End If
                If g_objSettings.Setting("SHEET_INVOICENUMBER") <> "" Then
                    If SheetColumn(g_objSettings.Setting("SHEET_INVOICENUMBER")) > 0 Then
                        With .appendChild(xmlDoc.createElement("ns:CustomerReferences"))
                            .appendChild(xmlDoc.createElement("ns:CustomerReferenceType")).Text = "INVOICE_NUMBER"
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_INVOICENUMBER")))
                        End With
                    End If
                End If
                If g_objSettings.Setting("SHEET_PONUMBER") <> "" Then
                    If SheetColumn(g_objSettings.Setting("SHEET_PONUMBER")) > 0 Then
                        With .appendChild(xmlDoc.createElement("ns:CustomerReferences"))
                            .appendChild(xmlDoc.createElement("ns:CustomerReferenceType")).Text = "P_O_NUMBER"
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_PONUMBER")))
                        End With
                    End If
                End If
                
                If 0 <> InStr(1, ServiceType(lngRow, strCountry), "GROUND") _
                Or 0 <> InStr(1, ServiceType(lngRow, strCountry), "HOME") Then
                    If OptionTest("direct", "sign") Then
                        With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))
                            ' Hard code special
                            With .appendChild(xmlDoc.createElement("ns:SignatureOptionDetail"))
                                .appendChild(xmlDoc.createElement("ns:OptionType")).Text = "DIRECT"
                            End With
                        End With
                    End If
                Else ' Express
                    If OptionTest("adult", "sign") Then
                        With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))
                            ' Hard code special
                            With .appendChild(xmlDoc.createElement("ns:SignatureOptionDetail"))
                                .appendChild(xmlDoc.createElement("ns:OptionType")).Text = "ADULT"
                            End With
                        End With
                    ElseIf OptionTest("indirect", "sign") Then
                        With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))
                            ' Hard code special
                            With .appendChild(xmlDoc.createElement("ns:SignatureOptionDetail"))
                                .appendChild(xmlDoc.createElement("ns:OptionType")).Text = "INDIRECT"
                            End With
                        End With
                    ElseIf OptionTest("no", "sign") Then
                        With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))
                            ' Hard code special
                            With .appendChild(xmlDoc.createElement("ns:SignatureOptionDetail"))
                                .appendChild(xmlDoc.createElement("ns:OptionType")).Text = "NO_SIGNATURE_REQUIRED"
                            End With
                        End With
                    End If
                End If
                
            End With ' RequestedPackageLineItems
        End With ' RequestedShipment
    End With ' ProcessShipmentRequest

    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & lngRow & " Request.xml", True)
        .Write xmlDoc.FirstChild.XML
        .Close
    End With

    g_strConfigHostEnvironment = g_objSettings.Setting("API_ENVIRONMENT")
    strReply = SendRequestToGateway(xmlDoc.FirstChild.XML)

    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & lngRow & " Reply.xml", True)
        .Write strReply
        .Close
    End With
    Set xmlReply = New DOMDocument
    xmlReply.loadXML strReply

    mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))).Font.ColorIndex = 1

    If xmlReply.selectSingleNode("v10:ProcessShipmentReply") Is Nothing Then
        mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = "Unknown reply:  " & strReply
        mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))).Font.ColorIndex = 3
    End If

    For Each xmlnode1 In xmlReply.selectNodes("v10:ProcessShipmentReply")
        For Each xmlNode2 In xmlnode1.selectNodes("v10:Notifications")
            For Each xmlNode3 In xmlNode2.selectNodes("v10:LocalizedMessage")
                If xmlNode3.Text <> "Success" Then
                If 0 = InStr(1, xmlNode3.Text, "last piece of the multiple piece shipment") Then
                    ' 2010-06-01, JFW, New Notifications column for problems with the shipment
                    If mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = "" Then
                        mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = "Errors or warnings:  "
                    End If
                    mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) _
                        & vbLf & xmlNode3.Text
                    mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")).Font.ColorIndex = 3
                End If ' MPS rate notice
                End If
            Next
        Next
    Next

    If 0 <> InStr(1, strReply, "<v10:Image>") Then
        
        For Each xmlnode1 In xmlReply.selectNodes("v10:ProcessShipmentReply")
            For Each xmlNode2 In xmlnode1.selectNodes("v10:CompletedShipmentDetail")
                For Each xmlNode3 In xmlNode2.selectNodes("v10:ShipmentRating")
                    For Each xmlNode4 In xmlNode3.selectNodes("v10:ShipmentRateDetails")
                        For Each xmlNode5 In xmlNode4.selectNodes("v10:TotalNetCharge")
                        For Each xmlNode6 In xmlNode5.selectNodes("v10:Amount")
                            mobjWorksheet.Cells(lngRow, SheetColumn("NetCharge")) = xmlNode6.Text
                            mobjWorksheet.Cells(lngRow, SheetColumn("NetCharge")).NumberFormat = FORMAT_AMOUNT
                        Next
                        Next ' TotalNetCharge
                    Next ' ShipmentRateDetails
                Next ' ShipmentRating
                
                For Each xmlNode3 In xmlNode2.selectNodes("v10:ShipmentDocuments")
                    If xmlNode3.selectSingleNode("v10:Type").Text = "COMMERCIAL_INVOICE" Then
                        For Each xmlNode5 In xmlNode3.selectNodes("v10:Parts")
                            For Each xmlNode6 In xmlNode5.selectNodes("v10:Image")
                                strCI = xmlNode6.Text
                                Exit For ' First part only
                            Next
                        Next ' Parts
                    End If ' COMMERCIAL_INVOICE
                Next ' ShipmentDocuments
                
                For Each xmlNode3 In xmlNode2.selectNodes("v10:CompletedPackageDetails")
                    For Each xmlNode4 In xmlNode3.selectNodes("v10:TrackingIds")
                    
                        APIBatchReturnOne = True
                        
                        For Each xmlNode5 In xmlNode4.selectNodes("v10:FormId") ' //zp new
                            mobjWorksheet.Cells(lngRow, SheetColumn("FormId")) = xmlNode5.Text
                        Next
                        
                        For Each xmlNode5 In xmlNode4.selectNodes("v10:TrackingNumber")
                            mobjWorksheet.Cells(lngRow, SheetColumn("LabelDate")) = Now
                            mobjWorksheet.Cells(lngRow, SheetColumn("LabelDate")).NumberFormat = FORMAT_DATEONLY
                            strTrackingNbr = xmlNode5.Text
                            ' 2011-08-19, JFW, Force text format on numeric tracking number
                            mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = "'" & strTrackingNbr
                            ' mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))).NumberFormat = FORMAT_NUMERIC
                        Next ' TrackingNumber
                    Next ' TrackingIds
                    
                    For Each xmlNode4 In xmlNode3.selectNodes("v10:Label")
                        For Each xmlNode5 In xmlNode4.selectNodes("v10:Parts")
                            For Each xmlNode6 In xmlNode5.selectNodes("v10:Image")
                                strLabel = xmlNode6.Text
                                Exit For ' First part only
                            Next
                        Next
                    Next ' Label
                    
                Next
            Next
        Next
        
        If False And strLabel = "" Then
            strLabel = Mid(strReply, InStr(1, strReply, "<v10:Image>"), Len(strReply))
            strLabel = Left(strLabel, InStr(1, strLabel, "</v10:Image>") - 1)
            strLabel = Right(strLabel, Len(strLabel) - Len("<v10:Image>"))
        End If
        If strLabel <> "" Then
            ' Use any Base64 coder/decoder
            strInsert = DecodeStr64(strLabel)
            ' Label by customer name
            If g_objSettings.Setting("SHEET_COMPANY") <> "" Then
                strLabelFilename = QuoteTrim(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_COMPANY")))) & " - " & strTrackingNbr
            Else
                strLabelFilename = lngRow & " - " & strTrackingNbr
            End If
            strLabelFilename = Replace(strLabelFilename, "/", "_")
            strLabelFilename = Replace(strLabelFilename, "\", "_")
            strLabelFilename = Replace(strLabelFilename, ".", "_")
            If g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PDF" Then
                strLabelFilename = strLabelFilename & ".pdf"
            ElseIf g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PNG" Then
                strLabelFilename = strLabelFilename & ".png"
            Else
                strLabelFilename = strLabelFilename & ".txt"
            End If
            intFile = FreeFile
            Open g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename For Output As #intFile
            Print #intFile, strInsert
            Close #intFile
        
            ' Label by tracking number
            strLabelFilename = strTrackingNbr
            If g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PDF" Then
                strLabelFilename = strLabelFilename & ".pdf"
            ElseIf g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PNG" Then
                strLabelFilename = strLabelFilename & ".png"
            Else
                strLabelFilename = strLabelFilename & ".txt"
            End If
            intFile = FreeFile
            Open g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename For Output As #intFile
            Print #intFile, strInsert
            Close #intFile
        
            If g_objSettings.Setting("PRINTER") <> "" Then
                If g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PDF" Then
                    Shell """" & g_objSettings.SettingWithDefault("AdobeReaderPath", "AcroRd32.exe") & """" & " /t" _
                        & " " & """" & g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename & """" _
                        & " " & """" & g_objSettings.Setting("PRINTER") & """" & "", vbNormalFocus
                ElseIf g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PNG" Then
                    Shell """C:\Program Files\Internet Explorer\iexplore.exe"" """ & g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename & """", vbNormalFocus
                Else
                    g_objFSO.CopyFile g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename, g_objSettings.Setting("PRINTER")
                End If
            End If
        End If
        
        If strCI <> "" Then
            ' Use any Base64 coder/decoder
            strInsert = DecodeStr64(strCI)
            ' Temporary label file
            If g_objSettings.Setting("SHEET_COMPANY") <> "" Then
                strLabelFilename = QuoteTrim(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_COMPANY")))) & " - " & strTrackingNbr
            Else
                strLabelFilename = lngRow & " - " & strTrackingNbr
            End If
            strLabelFilename = Replace(strLabelFilename, "/", "_")
            strLabelFilename = Replace(strLabelFilename, "\", "_")
            strLabelFilename = Replace(strLabelFilename, ".", "_")
            strLabelFilename = strLabelFilename & "_CI.pdf"
            intFile = FreeFile
            Open g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename For Output As #intFile
            Print #intFile, strInsert
            Close #intFile
        End If ' CI
        
        Set xmlMail = New DOMDocument
        With xmlMail.appendChild(xmlMail.createElement("MailMessage"))
            .appendChild(xmlMail.createElement("To")).Text = "jfweir@hotmail.com"
            .appendChild(xmlMail.createElement("Subject")).Text = "FedEx shipment label attached"
            .appendChild(xmlMail.createElement("Body")).Text = "See FedEx tracking at" _
                & vbCrLf & "http://www.fedex.com/Tracking?tracknumber_list=" & strTrackingNbr
            .appendChild(xmlMail.createElement("Attachment")).Text = g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename
        End With
        If g_objSettings.Setting("MAIL_FOLDER") <> "" Then
            With g_objFSO.CreateTextFile(g_objSettings.Setting("MAIL_FOLDER") & strTrackingNbr & ".xml", True)
                .Write xmlMail.XML
                .Close
            End With
        End If
        
    End If
    Exit Function
    
ErrorExit:
    Err.Raise 12345, "FSMAPI", "Error during ShipOne, " & Err.Source & ", " & Err.Description
    Resume
    Resume Next
End Function
'


Public Sub APIBatchShip()

    Dim objWorkbook As Workbook
    Dim lngRow As Long, j As Long
    Dim lngShipTotal As Long
    Dim lngShipTry As Long
    Dim strShipDate As String
    Dim boolShipIt As Boolean
    Dim boolProcess As Boolean
    
    mblnStop = False
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    Set objWorkbook = ActiveWorkbook
    Set mobjWorksheet = ActiveSheet
    
'    For Each objWorkbook In Workbooks
    
'        For Each mobjWorksheet In objWorkbook.Sheets
            
            Call APIBatchMPS
            
            lngRow = CLng(g_objSettings.Setting("HEADER_ROW")) + 1
            Debug.Print mobjWorksheet.Name
            ' 2010-07-27, ZP, Add form id column for MPS
            SheetColumn ("FormId")
            Do ' each row of the sheet
                If SheetColumn(g_objSettings.Setting("SHEET_CONTACT")) < 1 Then
                    Exit Do
                End If
                If mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_CONTACT"))) = "" Then
                    Exit Do
                End If
                If mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = "" Then
                    boolProcess = False
                    If Not boolShipDate Then
                        boolProcess = True
                    ElseIf IsDate(mobjWorksheet.Cells(lngRow, lngShipDate)) Then
                        If CDate(mobjWorksheet.Cells(lngRow, lngShipDate)) = mdtShipDate Then
                            boolProcess = True
                        End If
                    End If
                    If boolProcess Then

                        ' 2011-11-25, JFW, Blank notifications for attempted shipment
                        If SheetColumnExists("Notifications") Then
                            If mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) <> "" Then
                                mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = ""
                            End If
                        End If

                        boolShipIt = False
                        If lngPackageCount <= 0 Then
                            boolShipIt = True
                        ElseIf lngPackageNumber <= 0 Then
                            boolShipIt = True
                        ElseIf Not IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageCount)) Then
                            boolShipIt = True
                        ElseIf Not CLng(mobjWorksheet.Cells(lngRow, lngPackageCount)) = 1 Then
                            boolShipIt = True
                        ElseIf Not IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageNumber)) Then
                            boolShipIt = False
                        ElseIf IsNumeric(mobjWorksheet.Cells(lngRow + 1 - mobjWorksheet.Cells(lngRow, lngPackageNumber), SheetColumn(g_objSettings.Setting("TrackingNumber")))) Then
                            boolShipIt = True
                        Else
                            mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = "This row fails single/MPS testing."
                            mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")).Font.ColorIndex = 3
                            boolShipIt = False
                        End If
                        
                        If boolShipIt Then
                            lngShipTry = lngShipTry + 1
                            If APIBatchShipOne(lngRow) Then
                                lngShipTotal = lngShipTotal + 1
                            End If
                        End If
                        
                    End If
                End If
                lngRow = lngRow + 1
                DoEvents
                If mblnStop Then Exit Do
            Loop ' each row of the sheet
            
            ' 2010-07-27, ZP, Remove form id
            DeleteColumn ("FormId")
            ' First sheet only
'            Exit For
            
'        Next mobjWorksheet
        
        ' Exit For
'    Next objWorkbook
    
    MsgBox "Batch complete." & vbCrLf _
        & lngShipTry & " records." & vbCrLf _
        & lngShipTotal & " shipments."
    Exit Sub
    
End Sub




Public Sub APIBatchShipCopyRow(lngOrigin, lngDestination)

    With mobjWorksheet
        .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_COUNTRYCODE"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_COUNTRYCODE")))
        If SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE"), True) > 0 Then
            .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE")))
        End If
        If SheetColumn(g_objSettings.Setting("SHEET_PACKAGETYPE"), True) > 0 Then
            .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_PACKAGETYPE"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_PACKAGETYPE")))
        End If
        If SheetColumnExists("Weight") Then
            .Cells(lngDestination, SheetColumn("Weight")) = .Cells(lngOrigin, SheetColumn("Weight"))
        End If
        If SheetColumnExists("Length") Then
            .Cells(lngDestination, SheetColumn("Length")) = .Cells(lngOrigin, SheetColumn("Length"))
        End If
        If SheetColumnExists("Width") Then
            .Cells(lngDestination, SheetColumn("Width")) = .Cells(lngOrigin, SheetColumn("Width"))
        End If
        If SheetColumnExists("Height") Then
            .Cells(lngDestination, SheetColumn("Height")) = .Cells(lngOrigin, SheetColumn("Height"))
        End If
        If SheetColumnExists("Options") Then
            .Cells(lngDestination, SheetColumn("Options")) = .Cells(lngOrigin, SheetColumn("Options"))
        End If
        If SheetColumnExists("ShipDate") Then
            .Cells(lngDestination, SheetColumn("ShipDate")) = .Cells(lngOrigin, SheetColumn("ShipDate"))
        End If
        .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_CONTACT"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_CONTACT")))
        .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_COMPANY"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_COMPANY")))
        .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_PHONE"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_PHONE")))
        .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS1"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS1")))
        If g_objSettings.Setting("SHEET_ADDRESS2") <> "" Then
            .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS2"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS2")))
        End If

        .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_CITY"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_CITY")))
        .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_STATE"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_STATE")))
        .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_ZIP"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_ZIP")))
        If g_objSettings.Setting("SHEET_REFERENCE") <> "" Then
            .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_REFERENCE"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_REFERENCE")))
        End If
        If g_objSettings.Setting("SHEET_INVOICENUMBER") <> "" Then
            .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_INVOICENUMBER"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_INVOICENUMBER")))
        End If
        If g_objSettings.Setting("SHEET_PONUMBER") <> "" Then
            .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_PONUMBER"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_PONUMBER")))
        End If
    
        If g_objSettings.Setting("SHEET_EMAIL") <> "" Then
            .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_EMAIL"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_PONUMBER")))
        End If
        If g_objSettings.Setting("SHEET_TAXID") <> "" Then
            .Cells(lngDestination, SheetColumn(g_objSettings.Setting("SHEET_TAXID"))) = .Cells(lngOrigin, SheetColumn(g_objSettings.Setting("SHEET_TAXID")))
        End If
        If lngShipDate > 0 Then
            .Cells(lngDestination, lngShipDate) = .Cells(lngOrigin, lngShipDate)
        End If
    End With

End Sub

Public Function APIBatchShipOne(lngRow As Long) As Boolean

    On Error GoTo ErrorExit
    
    Dim intFile As Long
    Dim strInsert As String

    Dim i As Long
    Dim strCountry As String
    Dim strCI As String
    Dim strLabel As String
    Dim strReply As String
    Dim strMasterAwb, strFormId, strPackageCnt As String
    Dim boolEmail As Boolean
    Dim boolCommodity As Boolean
    Dim lngCommodity As Long
    Dim strCommodity() As String
    Dim strCommodityList() As String
    Dim dblCustomsValue As Double
    Dim dblCommodityWeight As Double
    Dim dblCommodityCount As Double
    Dim obj As Object
    Dim xmlnode1 As IXMLDOMNode
    Dim xmlNode2 As IXMLDOMNode
    Dim xmlNode3 As IXMLDOMNode
    Dim xmlNode4 As IXMLDOMNode
    Dim xmlNode5 As IXMLDOMNode
    Dim xmlNode6 As IXMLDOMNode
    Dim xmlNode7 As IXMLDOMNode
    
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    Set xmlDoc = New DOMDocument

    If SheetColumn("Options", True) > 0 Then
        strOption = OptionSplit(mobjWorksheet.Cells(lngRow, SheetColumn("Options")))
    Else
        ReDim strOption(0)
    End If

    With xmlDoc.appendChild(xmlDoc.createElement("ns:ProcessShipmentRequest"))
        Set obj = xmlDoc.createAttribute("xmlns:ns")
        obj.Text = "http://fedex.com/ws/ship/v10"
        Call .Attributes.setNamedItem(obj)
        
        Set obj = xmlDoc.createAttribute("xmlns:xsi")
        obj.Text = "http://www.w3.org/2001/XMLSchema-instance"
        Call .Attributes.setNamedItem(obj)
        
        Set obj = xmlDoc.createAttribute("xsi:schemaLocation")
        obj.Text = "http://fedex.com/ws/ship/v10/ShipService_v10.xsd"
        Call .Attributes.setNamedItem(obj)
        
        With .appendChild(xmlDoc.createElement("ns:WebAuthenticationDetail"))
            With .appendChild(xmlDoc.createElement("ns:UserCredential"))
                .appendChild(xmlDoc.createElement("ns:Key")).Text = g_objSettings.Setting("WS_KEY")
                If g_objSettings.Setting("WS_PASSWORD") <> "" Then
                    .appendChild(xmlDoc.createElement("ns:Password")).Text = g_objSettings.Setting("WS_PASSWORD")
                End If
            End With
        End With
        With .appendChild(xmlDoc.createElement("ns:ClientDetail"))
            .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
            .appendChild(xmlDoc.createElement("ns:MeterNumber")).Text = g_objSettings.Setting("METER_NUMBER")
        End With
        With .appendChild(xmlDoc.createElement("ns:TransactionDetail"))
            .appendChild(xmlDoc.createElement("ns:CustomerTransactionId")).Text = lngRow
        End With
        With .appendChild(xmlDoc.createElement("ns:Version"))
            .appendChild(xmlDoc.createElement("ns:ServiceId")).Text = "ship"
            .appendChild(xmlDoc.createElement("ns:Major")).Text = "10"
            .appendChild(xmlDoc.createElement("ns:Intermediate")).Text = "0"
            .appendChild(xmlDoc.createElement("ns:Minor")).Text = "0"
        End With
        strCountry = CountryConv(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_COUNTRYCODE"))))
        With .appendChild(xmlDoc.createElement("ns:RequestedShipment"))
            If lngShipDate > 0 Then
                If IsDate(mobjWorksheet.Cells(lngRow, lngShipDate)) Then
                    .appendChild(xmlDoc.createElement("ns:ShipTimestamp")).Text = Format(CDate(mobjWorksheet.Cells(lngRow, lngShipDate)), "yyyy-mm-dd") & "T" & Format(Now, "HH:nn:ss") & "-05:00"
                End If
            Else
                .appendChild(xmlDoc.createElement("ns:ShipTimestamp")).Text = Format(Date, "yyyy-mm-dd") & "T" & Format(Now, "HH:nn:ss") & "-05:00"
            End If
            
            .appendChild(xmlDoc.createElement("ns:DropoffType")).Text = "REGULAR_PICKUP"
            .appendChild(xmlDoc.createElement("ns:ServiceType")).Text = ServiceType(lngRow, strCountry)
            .appendChild(xmlDoc.createElement("ns:PackagingType")).Text = PackageType(lngRow)
                        
            With .appendChild(xmlDoc.createElement("ns:TotalWeight"))
                .appendChild(xmlDoc.createElement("ns:Units")).Text = g_objSettings.Setting("WEIGHT_TYPE")
                If SheetColumnExists("TotalWgt") Then
                    If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt"))) Then
                        If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt"))) > 0 Then
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("TotalWgt")))
                        Else
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                        End If
                    Else
                        .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                    End If
                ElseIf SheetColumnExists("Weight") Then
                    If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) Then
                        If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) > 0 Then
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight")))
                        Else
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                        End If
                    Else
                        .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                    End If
                Else
                    .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                End If
            End With
            With .appendChild(xmlDoc.createElement("ns:Shipper"))
                With .appendChild(xmlDoc.createElement("ns:Contact"))
                    .appendChild(xmlDoc.createElement("ns:PersonName")).Text = g_objSettings.Setting("SENDER_CONTACT")
                    .appendChild(xmlDoc.createElement("ns:CompanyName")).Text = g_objSettings.Setting("SENDER_COMPANY")
                    .appendChild(xmlDoc.createElement("ns:PhoneNumber")).Text = g_objSettings.Setting("SENDER_PHONE")
                End With
                With .appendChild(xmlDoc.createElement("ns:Address"))
                    .appendChild(xmlDoc.createElement("ns:StreetLines")).Text = g_objSettings.Setting("SENDER_ADDR1")
                    .appendChild(xmlDoc.createElement("ns:City")).Text = g_objSettings.Setting("SENDER_CITY")
                    .appendChild(xmlDoc.createElement("ns:StateOrProvinceCode")).Text = g_objSettings.Setting("SENDER_STPV")
                    .appendChild(xmlDoc.createElement("ns:PostalCode")).Text = g_objSettings.Setting("SENDER_ZIP")
                    .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                End With
            End With
            With .appendChild(xmlDoc.createElement("ns:Recipient"))
                With .appendChild(xmlDoc.createElement("ns:Contact"))
                    .appendChild(xmlDoc.createElement("ns:PersonName")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_CONTACT")))
                    .appendChild(xmlDoc.createElement("ns:CompanyName")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_COMPANY")))
                    .appendChild(xmlDoc.createElement("ns:PhoneNumber")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_PHONE")))
                End With
                With .appendChild(xmlDoc.createElement("ns:Address"))
                    .appendChild(xmlDoc.createElement("ns:StreetLines")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS1")))
                    If g_objSettings.Setting("SHEET_ADDRESS2") <> "" Then
                        If mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS2"))) <> "" Then
                            .appendChild(xmlDoc.createElement("ns:StreetLines")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ADDRESS2")))
                        End If
                    End If
                    .appendChild(xmlDoc.createElement("ns:City")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_CITY")))
                    .appendChild(xmlDoc.createElement("ns:StateOrProvinceCode")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_STATE")))
                    .appendChild(xmlDoc.createElement("ns:PostalCode")).Text = ZipConv( _
                                    strCountry, _
                                    mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_ZIP"))))
                    .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = strCountry
                    If OptionTest("resi") Then
                        .appendChild(xmlDoc.createElement("ns:Residential")).Text = "true"
                    End If
                End With
            End With
            With .appendChild(xmlDoc.createElement("ns:ShippingChargesPayment"))
                If OptionIndex(strOption, "Bill") >= 0 And OptionIndex(strOption, "Bill") = (OptionIndex(strOption, "Recip") - 1) Then
                    .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "RECIPIENT"
                    i = OptionIndex(strOption, "Recip") + 1
                ElseIf OptionIndex(strOption, "BillRecip") >= 0 Then
                    .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "RECIPIENT"
                    i = OptionIndex(strOption, "BillRecip") + 1
                ElseIf OptionIndex(strOption, "Bill") >= 0 And OptionIndex(strOption, "Bill") = (OptionIndex(strOption, "Third") - 1) Then
                    .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "THIRD_PARTY"
                    i = OptionIndex(strOption, "Third") + 1
                ElseIf OptionIndex(strOption, "BillThird") >= 0 Then
                    .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "THIRD_PARTY"
                    i = OptionIndex(strOption, "BillThird") + 1
                Else
                    .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "SENDER"
                    With .appendChild(xmlDoc.createElement("ns:Payor"))
                        .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
                        .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                    End With
                     i = -1
                End If
                If i >= 0 Then
                    If i <= UBound(strOption) Then
                        With .appendChild(xmlDoc.createElement("ns:Payor"))
                            .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = strOption(i)
                            If i + 1 <= UBound(strOption) Then
                                .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = strOption(i + 1)
                            Else
                                .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                            End If
                        End With
                    Else
                        With .appendChild(xmlDoc.createElement("ns:Payor"))
                            .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = "123456789"
                            .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                        End With
                    End If
                End If
            End With
            
            If 0 <> InStr(1, ServiceType(lngRow, strCountry), "FREIGHT") Then
                With .appendChild(xmlDoc.createElement("ns:ExpressFreightDetail"))
                    .appendChild(xmlDoc.createElement("ns:ShippersLoadAndCount")).Text = "1"
                    .appendChild(xmlDoc.createElement("ns:BookingConfirmationNumber")).Text = "123456"
                End With ' Special services
            End If
            
            ' 2012-10-19, JFW, Can't have Special on freight.
            If 0 = InStr(1, ServiceType(lngRow, strCountry), "FREIGHT") Then
            ' Always on because we will (at least) have sender email
            With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))

                If lngShipDate > 0 Then
                If IsDate(mobjWorksheet.Cells(lngRow, lngShipDate)) Then
                If CDate(mobjWorksheet.Cells(lngRow, lngShipDate)) > Date Then
                    .appendChild(xmlDoc.createElement("ns:SpecialServiceTypes")).Text = "FUTURE_DAY_SHIPMENT"
                End If
                End If
                End If
            
                If OptionIndex(strOption, "Saturday") >= 0 Then
                    .appendChild(xmlDoc.createElement("ns:SpecialServiceTypes")).Text = "SATURDAY_DELIVERY"
                Else
                    ' .appendChild(xmlDoc.createElement("ns:SpecialServiceTypes")).Text = "WEEKDAY_DELIVERY"
                End If
                
                ' ETD goes here
                If g_objSettings.Setting("ETD") <> "" Then
                If g_objSettings.Setting("ETD") Then
                If SheetColumn("Options") > 0 Then
                If 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn("Options"))), "etd") Then
                If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                    .appendChild(xmlDoc.createElement("ns:SpecialServiceTypes")).Text = "ELECTRONIC_TRADE_DOCUMENTS"
                    With .appendChild(xmlDoc.createElement("ns:EtdDetail"))
                        .appendChild(xmlDoc.createElement("ns:RequestedDocumentCopies")).Text = "COMMERCIAL_INVOICE"
                    End With
                End If
                End If
                End If
                End If
                End If
                
                boolEmail = False
                If g_objSettings.Setting("SENDER_EMAIL") <> "" Then
                    boolEmail = True
                End If
                If g_objSettings.Setting("SHEET_EMAIL") <> "" Then
                If SheetColumn(g_objSettings.Setting("SHEET_EMAIL")) > 0 Then
                If mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_EMAIL"))) <> "" Then
                    boolEmail = True
                End If
                End If
                End If
                    
                If boolEmail Then
                With .appendChild(xmlDoc.createElement("ns:EMailNotificationDetail"))
                    If g_objSettings.Setting("SENDER_EMAIL") <> "" Then
                        With .appendChild(xmlDoc.createElement("ns:Recipients"))
                            .appendChild(xmlDoc.createElement("ns:EMailNotificationRecipientType")).Text = "SHIPPER"
                            .appendChild(xmlDoc.createElement("ns:EMailAddress")).Text = g_objSettings.Setting("SENDER_EMAIL")
                            'NotificationEventsRequested
                            '.appendChild(xmlDoc.createElement("ns:NotifyOnShipment")).Text = "true"
                            '.appendChild(xmlDoc.createElement("ns:NotifyOnException")).Text = "true"
                            '.appendChild(xmlDoc.createElement("ns:NotifyOnDelivery")).Text = "true"
                            .appendChild(xmlDoc.createElement("ns:NotificationEventsRequested")).Text = "ON_TENDER"
                            .appendChild(xmlDoc.createElement("ns:NotificationEventsRequested")).Text = "ON_EXCEPTION"
                            .appendChild(xmlDoc.createElement("ns:NotificationEventsRequested")).Text = "ON_DELIVERY"
                            .appendChild(xmlDoc.createElement("ns:Format")).Text = "HTML"
                            With .appendChild(xmlDoc.createElement("ns:Localization"))
                                .appendChild(xmlDoc.createElement("ns:LanguageCode")).Text = "EN"
                            End With
                        End With
                    End If
                    If g_objSettings.Setting("SHEET_EMAIL") <> "" Then
                    If SheetColumn(g_objSettings.Setting("SHEET_EMAIL")) > 0 Then
                    If mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_EMAIL"))) <> "" Then
                        With .appendChild(xmlDoc.createElement("ns:Recipients"))
                            .appendChild(xmlDoc.createElement("ns:EMailNotificationRecipientType")).Text = "RECIPIENT"
                            .appendChild(xmlDoc.createElement("ns:EMailAddress")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_EMAIL")))
                            'NotificationEventsRequested
                            '.appendChild(xmlDoc.createElement("ns:NotifyOnShipment")).Text = "true"
                            '.appendChild(xmlDoc.createElement("ns:NotifyOnException")).Text = "true"
                            '.appendChild(xmlDoc.createElement("ns:NotifyOnDelivery")).Text = "true"
                            .appendChild(xmlDoc.createElement("ns:NotificationEventsRequested")).Text = "ON_TENDER"
                            .appendChild(xmlDoc.createElement("ns:NotificationEventsRequested")).Text = "ON_EXCEPTION"
                            .appendChild(xmlDoc.createElement("ns:NotificationEventsRequested")).Text = "ON_DELIVERY"
                            .appendChild(xmlDoc.createElement("ns:Format")).Text = "HTML"
                            With .appendChild(xmlDoc.createElement("ns:Localization"))
                                .appendChild(xmlDoc.createElement("ns:LanguageCode")).Text = "EN"
                            End With
                        End With
                    End If
                    End If
                    End If
                End With ' Email
                End If ' Email
                
            End With ' Special services
            End If
            ' temp
            
            If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                With .appendChild(xmlDoc.createElement("ns:CustomsClearanceDetail"))
                    
                    With .appendChild(xmlDoc.createElement("ns:DutiesPayment"))
                        If OptionIndex(strOption, "Duty") >= 0 And OptionIndex(strOption, "Duty") = (OptionIndex(strOption, "Consignee") - 1) Then
                            .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "RECIPIENT"
                            i = -1
                        ElseIf OptionIndex(strOption, "DutyConsignee") >= 0 Then
                            .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "RECIPIENT"
                            i = -1
                        ElseIf OptionIndex(strOption, "Duty") >= 0 And OptionIndex(strOption, "Duty") = (OptionIndex(strOption, "Recip") - 1) Then
                            .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "RECIPIENT"
                            i = OptionIndex(strOption, "Recip") + 1
                        ElseIf OptionIndex(strOption, "DutyRecip") >= 0 Then
                            .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "RECIPIENT"
                            i = OptionIndex(strOption, "DutyRecip") + 1
                        ElseIf OptionIndex(strOption, "Duty") >= 0 And OptionIndex(strOption, "Duty") = (OptionIndex(strOption, "Third") - 1) Then
                            .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "THIRD_PARTY"
                            i = OptionIndex(strOption, "Third") + 1
                        ElseIf OptionIndex(strOption, "DutyThird") >= 0 Then
                            .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "THIRD_PARTY"
                            i = OptionIndex(strOption, "DutyThird") + 1
                        Else
                            .appendChild(xmlDoc.createElement("ns:PaymentType")).Text = "SENDER"
                            With .appendChild(xmlDoc.createElement("ns:Payor"))
                                .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
                                .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                            End With
                             i = -1
                        End If
                        If i >= 0 Then
                            If i < UBound(strOption) Then
                                If IsNumeric(strOption(i)) Then
                                    With .appendChild(xmlDoc.createElement("ns:Payor"))
                                        .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = strOption(i)
                                        .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = strOption(i + 1) ' g_objSettings.Setting("SENDER_COUNTRY")
                                    End With
                                End If
'                            Else
'                                With .appendChild(xmlDoc.createElement("ns:Payor"))
'                                    .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = "123456789"
'                                    .appendChild(xmlDoc.createElement("ns:CountryCode")).Text = g_objSettings.Setting("SENDER_COUNTRY")
'                                End With
                            End If
                        End If
                    End With
                    
                    boolCommodity = False
                    If SheetColumn("Commodity", True) > 0 Then
                        If mobjWorksheet.Cells(lngRow, SheetColumn("Commodity")) <> "" Then
                            boolCommodity = True
                        End If
                    End If
                    If boolCommodity Then
                        
                        dblCustomsValue = 0
                        strCommodityList = Split(mobjWorksheet.Cells(lngRow, SheetColumn("Commodity")), Chr(10))
                        For lngCommodity = LBound(strCommodityList) To UBound(strCommodityList)
                            strCommodity = Split(strCommodityList(lngCommodity), ";")
                            dblCustomsValue = dblCustomsValue + CDbl(Trim(strCommodity(3)))
                        Next lngCommodity
                        dblCommodityCount = UBound(strCommodityList) + 1
                        
                        
                        .appendChild(xmlDoc.createElement("ns:DocumentContent")).Text = "NON_DOCUMENTS"
                        With .appendChild(xmlDoc.createElement("ns:CustomsValue"))
                            .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                            .appendChild(xmlDoc.createElement("ns:Amount")).Text = Format(dblCustomsValue, "0.00")
                        End With
                        strCommodityList = Split(mobjWorksheet.Cells(lngRow, SheetColumn("Commodity")), Chr(10))
                        For lngCommodity = LBound(strCommodityList) To UBound(strCommodityList)
                            strCommodity = Split(strCommodityList(lngCommodity), ";")
'                            If LBound(strCommodity) < 0 _
'                            Or UBound(strCommodity) <> 5 Then
'                                Err.Raise 12345, "BatchShip", "Commodity error, line " & lngRow & ", """ & strCommodityList(lngCommodity) & """, must be ""Description;Country;Quantity;Value;HSCode;Weight""."
'                            End If
                            With .appendChild(xmlDoc.createElement("ns:Commodities"))
                                .appendChild(xmlDoc.createElement("ns:NumberOfPieces")).Text = "1"
                                .appendChild(xmlDoc.createElement("ns:Description")).Text = Trim(strCommodity(0))
                                                            
                                .appendChild(xmlDoc.createElement("ns:CountryOfManufacture")).Text = Trim(strCommodity(1))
                                
                                If UBound(strCommodity) >= 4 Then
                                If Trim(strCommodity(4)) <> "" Then
                                    .appendChild(xmlDoc.createElement("ns:HarmonizedCode")).Text = Trim(strCommodity(4))
                                End If
                                End If
                                
                                With .appendChild(xmlDoc.createElement("ns:Weight"))
                                    .appendChild(xmlDoc.createElement("ns:Units")).Text = "LB"
                                    ' .appendChild(xmlDoc.createElement("ns:Value")).Text = Trim(strCommodity(5))
                                    dblCommodityWeight = Val(g_objSettings.Setting("SHIPPING_WEIGHT"))
                                    If SheetColumnExists("Weight") Then
                                        If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) Then
                                            If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) > 0 Then
                                                dblCommodityWeight = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight")))
                                            End If
                                        End If
                                    End If
                                    .appendChild(xmlDoc.createElement("ns:Value")).Text = dblCommodityWeight / dblCommodityCount
                                End With
                                
                                .appendChild(xmlDoc.createElement("ns:Quantity")).Text = Trim(strCommodity(2))
                                .appendChild(xmlDoc.createElement("ns:QuantityUnits")).Text = "EA"
                                With .appendChild(xmlDoc.createElement("ns:UnitPrice"))
                                    .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                                    .appendChild(xmlDoc.createElement("ns:Amount")).Text = CDbl(Trim(strCommodity(3))) / CDbl(Trim(strCommodity(2)))
                                End With
                                With .appendChild(xmlDoc.createElement("ns:CustomsValue"))
                                    .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                                    .appendChild(xmlDoc.createElement("ns:Amount")).Text = Trim(strCommodity(3))
                                End With
                            End With
                        Next lngCommodity
                    Else
                        ' Documents only
                        .appendChild(xmlDoc.createElement("ns:DocumentContent")).Text = "DOCUMENTS_ONLY"
                        With .appendChild(xmlDoc.createElement("ns:CustomsValue"))
                            .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                            .appendChild(xmlDoc.createElement("ns:Amount")).Text = CountryCustomsValue(strCountry)
                        End With
                        With .appendChild(xmlDoc.createElement("ns:Commodities"))
                            .appendChild(xmlDoc.createElement("ns:NumberOfPieces")).Text = "1"
                            .appendChild(xmlDoc.createElement("ns:Description")).Text = "Business Correspondence envelope"
                            .appendChild(xmlDoc.createElement("ns:CountryOfManufacture")).Text = g_objSettings.Setting("SENDER_COUNTRY")
                            With .appendChild(xmlDoc.createElement("ns:Weight"))
                                .appendChild(xmlDoc.createElement("ns:Units")).Text = "LB"
                                If SheetColumnExists("Weight") Then
                                    If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) Then
                                        If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) > 0 Then
                                            .appendChild(xmlDoc.createElement("ns:Value")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight")))
                                        Else
                                            .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                                        End If
                                    Else
                                        .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                                    End If
                                Else
                                    .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                                End If
                            End With
                            .appendChild(xmlDoc.createElement("ns:Quantity")).Text = "1"
                            .appendChild(xmlDoc.createElement("ns:QuantityUnits")).Text = "EA"
                            With .appendChild(xmlDoc.createElement("ns:UnitPrice"))
                                .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                                .appendChild(xmlDoc.createElement("ns:Amount")).Text = CountryCustomsValue(strCountry)
                            End With
                            With .appendChild(xmlDoc.createElement("ns:CustomsValue"))
                                .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                                .appendChild(xmlDoc.createElement("ns:Amount")).Text = CountryCustomsValue(strCountry)
                            End With
                        End With
                    End If ' Commodity
                    
                    If strCountry <> "US" Then
                        With .appendChild(xmlDoc.createElement("ns:ExportDetail"))
                            If dblCustomsValue >= 2000 Then
                                .appendChild(xmlDoc.createElement("ns:B13AFilingOption")).Text = "MANUALLY_ATTACHED"
                            Else
                                .appendChild(xmlDoc.createElement("ns:B13AFilingOption")).Text = "NOT_REQUIRED"
                            End If
                        End With
                    End If

                    If False And strCountry = "US" Then
                            If 0 <> InStr(1, LCase(PackageType(lngRow)), "env") Then
                                .appendChild(xmlDoc.createElement("ns:AdmissibilityPackageType")).Text = "ENV"
                            ElseIf 0 <> InStr(1, LCase(PackageType(lngRow)), "pak") Then
                                .appendChild(xmlDoc.createElement("ns:AdmissibilityPackageType")).Text = "BAG"
                            Else
                                .appendChild(xmlDoc.createElement("ns:AdmissibilityPackageType")).Text = "BOX"
                            End If
                    End If
                End With
            End If
                        
            With .appendChild(xmlDoc.createElement("ns:LabelSpecification"))
                .appendChild(xmlDoc.createElement("ns:LabelFormatType")).Text = g_objSettings.SettingWithDefault("LABEL_FORMAT_TYPE", "COMMON2D")
                .appendChild(xmlDoc.createElement("ns:ImageType")).Text = g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII")
                .appendChild(xmlDoc.createElement("ns:LabelStockType")).Text = g_objSettings.SettingWithDefault("LABEL_STOCK_TYPE", "STOCK_4X6.75_LEADING_DOC_TAB")
                If InStr(1, g_objSettings.SettingWithDefault("LABEL_STOCK_TYPE", "STOCK_4X6.75_LEADING_DOC_TAB"), "DOC_TAB") <> 0 Then
                    .appendChild(xmlDoc.createElement("ns:LabelPrintingOrientation")).Text = "TOP_EDGE_OF_TEXT_FIRST"
                    With .appendChild(xmlDoc.createElement("ns:CustomerSpecifiedDetail"))
                        With .appendChild(xmlDoc.createElement("ns:DocTabContent"))
                            .appendChild(xmlDoc.createElement("ns:DocTabContentType")).Text = "STANDARD"
                        End With
                    End With
                End If
            End With
            
            If g_objSettings.Setting("ETD") <> "" Then
            If g_objSettings.Setting("ETD") Then
            If SheetColumn("Options") > 0 Then
            If 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn("Options"))), "etd") Then
            If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                With .appendChild(xmlDoc.createElement("ns:ShippingDocumentSpecification"))
                    .appendChild(xmlDoc.createElement("ns:ShippingDocumentTypes")).Text = "COMMERCIAL_INVOICE"
                    With .appendChild(xmlDoc.createElement("ns:CommercialInvoiceDetail"))
                        With .appendChild(xmlDoc.createElement("ns:Format"))
                            .appendChild(xmlDoc.createElement("ns:ImageType")).Text = "PDF"
                            .appendChild(xmlDoc.createElement("ns:StockType")).Text = "PAPER_LETTER"
                        End With
                        If g_objSettings.Setting("CI_LETTERHEAD") <> "" Then
                            With .appendChild(xmlDoc.createElement("ns:CustomerImageUsages"))
                                .appendChild(xmlDoc.createElement("ns:Type")).Text = "LETTERHEAD"
                                .appendChild(xmlDoc.createElement("ns:Id")).Text = g_objSettings.Setting("CI_LETTERHEAD")
                            End With
                        End If
                        If g_objSettings.Setting("CI_SIGNATURE") <> "" Then
                            With .appendChild(xmlDoc.createElement("ns:CustomerImageUsages"))
                                .appendChild(xmlDoc.createElement("ns:Type")).Text = "SIGNATURE"
                                .appendChild(xmlDoc.createElement("ns:Id")).Text = g_objSettings.Setting("CI_SIGNATURE")
                            End With
                        End If
                    End With
                End With
            End If
            End If
            End If
            End If
            End If
                            
            .appendChild(xmlDoc.createElement("ns:RateRequestTypes")).Text = "ACCOUNT"

            strPackageCnt = ""
            If lngPackageNumber > 0 Then
            If IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageNumber)) Then
            If CLng(mobjWorksheet.Cells(lngRow, lngPackageNumber)) > 0 Then
                
                If CLng(mobjWorksheet.Cells(lngRow, lngPackageNumber)) > 1 Then
                    'MasterTrackingId is required for MPS child lines //zp new
                    strMasterAwb = mobjWorksheet.Cells(lngRow + 1 - mobjWorksheet.Cells(lngRow, _
                                    lngPackageNumber), SheetColumn(g_objSettings.Setting("TrackingNumber")))
                    strFormId = mobjWorksheet.Cells(lngRow + 1 - mobjWorksheet.Cells(lngRow, _
                                    lngPackageNumber), SheetColumn("FormId"))
                    If mobjWorksheet.Cells(lngRow, lngPackageCount) = "" And _
                            mobjWorksheet.Cells(lngRow, lngPackageNumber) <> "" And _
                            IsNumeric(strMasterAwb) Then
                        With .appendChild(xmlDoc.createElement("ns:MasterTrackingId"))
                            If strFormId <> "" Then
                                ' Express MPS
                                .appendChild(xmlDoc.createElement("ns:FormId")).Text = strFormId
                            End If
                            .appendChild(xmlDoc.createElement("ns:TrackingNumber")).Text = strMasterAwb
                        End With
                    End If
                End If
                
                'PackageCount is required for MPS master and child lines
                strPackageCnt = mobjWorksheet.Cells(lngRow + 1 - mobjWorksheet.Cells(lngRow, _
                                 lngPackageNumber), lngPackageCount)
                '//zp new end
            End If ' > 0
            End If ' numeric
            End If ' MPS_Id
            
            If IsNumeric(strPackageCnt) Then
                .appendChild(xmlDoc.createElement("ns:PackageCount")).Text = strPackageCnt
            Else
                .appendChild(xmlDoc.createElement("ns:PackageCount")).Text = "1"
            End If
            ' .appendChild(xmlDoc.createElement("ns:PackageDetail")).Text = "INDIVIDUAL_PACKAGES"
            
            With .appendChild(xmlDoc.createElement("ns:RequestedPackageLineItems"))
            
                'SequenceNumber is required for MPS //zp new
                If lngPackageNumber > 0 Then
                If mobjWorksheet.Cells(lngRow, lngPackageNumber) <> "" Then
                If IsNumeric(mobjWorksheet.Cells(lngRow, lngPackageNumber)) Then
                If CLng(mobjWorksheet.Cells(lngRow, lngPackageNumber)) > 0 Then
                    .appendChild(xmlDoc.createElement("ns:SequenceNumber")).Text = mobjWorksheet.Cells(lngRow, lngPackageNumber)
                End If ' //zp new end
                End If
                End If
                End If
                
                
                If SheetColumnExists("DeclaredValue") Then
                If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("DeclaredValue"))) Then
                If CLng(mobjWorksheet.Cells(lngRow, SheetColumn("DeclaredValue"))) > 0 Then
                    With .appendChild(xmlDoc.createElement("ns:InsuredValue"))
                        .appendChild(xmlDoc.createElement("ns:Currency")).Text = GetCurrency
                        .appendChild(xmlDoc.createElement("ns:Amount")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("DeclaredValue")))
                    End With
                End If
                End If
                End If
                
                With .appendChild(xmlDoc.createElement("ns:Weight"))
                    .appendChild(xmlDoc.createElement("ns:Units")).Text = g_objSettings.Setting("WEIGHT_TYPE")
                    If SheetColumnExists("Weight") Then
                        If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) Then
                            If CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight"))) > 0 Then
                                .appendChild(xmlDoc.createElement("ns:Value")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Weight")))
                            Else
                                .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                            End If
                        Else
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                        End If
                    Else
                        .appendChild(xmlDoc.createElement("ns:Value")).Text = g_objSettings.Setting("SHIPPING_WEIGHT")
                    End If
                End With
                
                If SheetColumnExists("Length") Then
                If IsNumeric(mobjWorksheet.Cells(lngRow, SheetColumn("Length"))) Then
                If CLng(mobjWorksheet.Cells(lngRow, SheetColumn("Length"))) > 0 Then
                    With .appendChild(xmlDoc.createElement("ns:Dimensions"))
                        .appendChild(xmlDoc.createElement("ns:Length")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Length")))
                        .appendChild(xmlDoc.createElement("ns:Width")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Width")))
                        .appendChild(xmlDoc.createElement("ns:Height")).Text = CDbl(mobjWorksheet.Cells(lngRow, SheetColumn("Height")))
                        .appendChild(xmlDoc.createElement("ns:Units")).Text = IIf(Left(g_objSettings.Setting("WEIGHT_TYPE"), 2) = "LB", "IN", "CM")
                    End With
                End If
                End If
                End If
                
                If SheetColumnExists("DIMS") Then
                If mobjWorksheet.Cells(lngRow, SheetColumn("DIMS")) <> "" Then
                If UBound(Split(LCase(mobjWorksheet.Cells(lngRow, SheetColumn("DIMS"))), "x")) >= 2 Then
                    With .appendChild(xmlDoc.createElement("ns:Dimensions"))
                        .appendChild(xmlDoc.createElement("ns:Length")).Text = CLng(Split(LCase(mobjWorksheet.Cells(lngRow, SheetColumn("DIMS"))), "x")(0))
                        .appendChild(xmlDoc.createElement("ns:Width")).Text = CLng(Split(LCase(mobjWorksheet.Cells(lngRow, SheetColumn("DIMS"))), "x")(1))
                        .appendChild(xmlDoc.createElement("ns:Height")).Text = CLng(Split(LCase(mobjWorksheet.Cells(lngRow, SheetColumn("DIMS"))), "x")(2))
                        .appendChild(xmlDoc.createElement("ns:Units")).Text = IIf(Left(g_objSettings.Setting("WEIGHT_TYPE"), 2) = "LB", "IN", "CM")
                    End With
                End If
                End If
                End If
                
                If g_objSettings.Setting("SHEET_REFERENCE") <> "" Then
                    If SheetColumn(g_objSettings.Setting("SHEET_REFERENCE")) > 0 Then
                        With .appendChild(xmlDoc.createElement("ns:CustomerReferences"))
                            .appendChild(xmlDoc.createElement("ns:CustomerReferenceType")).Text = "CUSTOMER_REFERENCE"
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_REFERENCE")))
                        End With
                    End If
                End If
                If g_objSettings.Setting("SHEET_INVOICENUMBER") <> "" Then
                    If SheetColumn(g_objSettings.Setting("SHEET_INVOICENUMBER")) > 0 Then
                        With .appendChild(xmlDoc.createElement("ns:CustomerReferences"))
                            .appendChild(xmlDoc.createElement("ns:CustomerReferenceType")).Text = "INVOICE_NUMBER"
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_INVOICENUMBER")))
                        End With
                    End If
                End If
                If g_objSettings.Setting("SHEET_PONUMBER") <> "" Then
                    If SheetColumn(g_objSettings.Setting("SHEET_PONUMBER")) > 0 Then
                        With .appendChild(xmlDoc.createElement("ns:CustomerReferences"))
                            .appendChild(xmlDoc.createElement("ns:CustomerReferenceType")).Text = "P_O_NUMBER"
                            .appendChild(xmlDoc.createElement("ns:Value")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_PONUMBER")))
                        End With
                    End If
                End If
                
                If 0 <> InStr(1, ServiceType(lngRow, strCountry), "GROUND") _
                Or 0 <> InStr(1, ServiceType(lngRow, strCountry), "HOME") Then
                    If OptionTest("direct", "sign") Then
                        With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))
                            ' Hard code special
                            With .appendChild(xmlDoc.createElement("ns:SignatureOptionDetail"))
                                .appendChild(xmlDoc.createElement("ns:OptionType")).Text = "DIRECT"
                            End With
                        End With
                    End If
                Else ' Express
                    If OptionTest("adult", "sign") Then
                        With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))
                            ' Hard code special
                            With .appendChild(xmlDoc.createElement("ns:SignatureOptionDetail"))
                                .appendChild(xmlDoc.createElement("ns:OptionType")).Text = "ADULT"
                            End With
                        End With
                    ElseIf OptionTest("indirect", "sign") Then
                        With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))
                            ' Hard code special
                            With .appendChild(xmlDoc.createElement("ns:SignatureOptionDetail"))
                                .appendChild(xmlDoc.createElement("ns:OptionType")).Text = "INDIRECT"
                            End With
                        End With
                    ElseIf OptionTest("no", "sign") Then
                        With .appendChild(xmlDoc.createElement("ns:SpecialServicesRequested"))
                            ' Hard code special
                            With .appendChild(xmlDoc.createElement("ns:SignatureOptionDetail"))
                                .appendChild(xmlDoc.createElement("ns:OptionType")).Text = "NO_SIGNATURE_REQUIRED"
                            End With
                        End With
                    End If
                End If
                
            End With ' RequestedPackageLineItems
        End With ' RequestedShipment
    End With ' ProcessShipmentRequest

    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & lngRow & " Request.xml", True)
        .Write xmlDoc.FirstChild.XML
        .Close
    End With

    g_strConfigHostEnvironment = g_objSettings.Setting("API_ENVIRONMENT")
    strReply = SendRequestToGateway(xmlDoc.FirstChild.XML)

    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & lngRow & " Reply.xml", True)
        .Write strReply
        .Close
    End With
    Set xmlReply = New DOMDocument
    xmlReply.loadXML strReply

    mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))).Font.ColorIndex = 1

    If xmlReply.selectSingleNode("v10:ProcessShipmentReply") Is Nothing Then
        mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = "Unknown reply:  " & strReply
        mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))).Font.ColorIndex = 3
    End If

    For Each xmlnode1 In xmlReply.selectNodes("v10:ProcessShipmentReply")
        For Each xmlNode2 In xmlnode1.selectNodes("v10:Notifications")
            For Each xmlNode3 In xmlNode2.selectNodes("v10:LocalizedMessage")
                If xmlNode3.Text <> "Success" Then
                If 0 = InStr(1, xmlNode3.Text, "last piece of the multiple piece shipment") Then
                    ' 2010-06-01, JFW, New Notifications column for problems with the shipment
                    If mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = "" Then
                        mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = "Errors or warnings:  "
                    End If
                    mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) _
                        & vbLf & xmlNode3.Text
                    mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")).Font.ColorIndex = 3
                End If ' MPS rate notice
                End If
            Next
        Next
    Next

    If 0 <> InStr(1, strReply, "<v10:Image>") Then
        
        For Each xmlnode1 In xmlReply.selectNodes("v10:ProcessShipmentReply")
            For Each xmlNode2 In xmlnode1.selectNodes("v10:CompletedShipmentDetail")
                For Each xmlNode3 In xmlNode2.selectNodes("v10:ShipmentRating")
                    For Each xmlNode4 In xmlNode3.selectNodes("v10:ShipmentRateDetails")
                        For Each xmlNode5 In xmlNode4.selectNodes("v10:TotalNetCharge")
                        For Each xmlNode6 In xmlNode5.selectNodes("v10:Amount")
                            mobjWorksheet.Cells(lngRow, SheetColumn("NetCharge")) = xmlNode6.Text
                            mobjWorksheet.Cells(lngRow, SheetColumn("NetCharge")).NumberFormat = FORMAT_AMOUNT
                        Next
                        Next ' TotalNetCharge
                    Next ' ShipmentRateDetails
                Next ' ShipmentRating
                
                For Each xmlNode3 In xmlNode2.selectNodes("v10:ShipmentDocuments")
                    If xmlNode3.selectSingleNode("v10:Type").Text = "COMMERCIAL_INVOICE" Then
                        For Each xmlNode5 In xmlNode3.selectNodes("v10:Parts")
                            For Each xmlNode6 In xmlNode5.selectNodes("v10:Image")
                                strCI = xmlNode6.Text
                                Exit For ' First part only
                            Next
                        Next ' Parts
                    End If ' COMMERCIAL_INVOICE
                Next ' ShipmentDocuments
                
                For Each xmlNode3 In xmlNode2.selectNodes("v10:CompletedPackageDetails")
                    For Each xmlNode4 In xmlNode3.selectNodes("v10:TrackingIds")
                    
                        APIBatchShipOne = True
                        
                        For Each xmlNode5 In xmlNode4.selectNodes("v10:FormId") ' //zp new
                            mobjWorksheet.Cells(lngRow, SheetColumn("FormId")) = xmlNode5.Text
                        Next
                        
                        For Each xmlNode5 In xmlNode4.selectNodes("v10:TrackingNumber")
                            mobjWorksheet.Cells(lngRow, SheetColumn("LabelDate")) = Now
                            mobjWorksheet.Cells(lngRow, SheetColumn("LabelDate")).NumberFormat = FORMAT_DATEONLY
                            strTrackingNbr = xmlNode5.Text
                            ' 2011-08-19, JFW, Force text format on numeric tracking number
                            mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = "'" & strTrackingNbr
                            ' mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))).NumberFormat = FORMAT_NUMERIC
                        Next ' TrackingNumber
                    Next ' TrackingIds
                    
                    For Each xmlNode4 In xmlNode3.selectNodes("v10:Label")
                        For Each xmlNode5 In xmlNode4.selectNodes("v10:Parts")
                            For Each xmlNode6 In xmlNode5.selectNodes("v10:Image")
                                strLabel = xmlNode6.Text
                                Exit For ' First part only
                            Next
                        Next
                    Next ' Label
                    
                Next
            Next
        Next
        
        If False And strLabel = "" Then
            strLabel = Mid(strReply, InStr(1, strReply, "<v10:Image>"), Len(strReply))
            strLabel = Left(strLabel, InStr(1, strLabel, "</v10:Image>") - 1)
            strLabel = Right(strLabel, Len(strLabel) - Len("<v10:Image>"))
        End If
        If strLabel <> "" Then
            ' Use any Base64 coder/decoder
            strInsert = DecodeStr64(strLabel)
            ' Label by customer name
            If g_objSettings.Setting("SHEET_COMPANY") <> "" Then
                strLabelFilename = QuoteTrim(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_COMPANY")))) & " - " & strTrackingNbr
            Else
                strLabelFilename = lngRow & " - " & strTrackingNbr
            End If
            strLabelFilename = Replace(strLabelFilename, "/", "_")
            strLabelFilename = Replace(strLabelFilename, "\", "_")
            strLabelFilename = Replace(strLabelFilename, ".", "_")
            If g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PDF" Then
                strLabelFilename = strLabelFilename & ".pdf"
            ElseIf g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PNG" Then
                strLabelFilename = strLabelFilename & ".png"
            Else
                strLabelFilename = strLabelFilename & ".txt"
            End If
            intFile = FreeFile
            Open g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename For Output As #intFile
            Print #intFile, strInsert
            Close #intFile
        
            ' Label by tracking number
            strLabelFilename = strTrackingNbr
            If g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PDF" Then
                strLabelFilename = strLabelFilename & ".pdf"
            ElseIf g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PNG" Then
                strLabelFilename = strLabelFilename & ".png"
            Else
                strLabelFilename = strLabelFilename & ".txt"
            End If
            intFile = FreeFile
            Open g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename For Output As #intFile
            Print #intFile, strInsert
            Close #intFile
        
            If g_objSettings.Setting("PRINTER") <> "" Then
                If g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PDF" Then
                    Shell """" & g_objSettings.SettingWithDefault("AdobeReaderPath", "AcroRd32.exe") & """" & " /t" _
                        & " " & """" & g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename & """" _
                        & " " & """" & g_objSettings.Setting("PRINTER") & """" & "", vbNormalFocus
                ElseIf g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PNG" Then
                    Shell """C:\Program Files\Internet Explorer\iexplore.exe"" """ & g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename & """", vbNormalFocus
                Else
                    g_objFSO.CopyFile g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename, g_objSettings.Setting("PRINTER")
                End If
            End If
        End If
        
        If strCI <> "" Then
            ' Use any Base64 coder/decoder
            strInsert = DecodeStr64(strCI)
            ' Temporary label file
            If g_objSettings.Setting("SHEET_COMPANY") <> "" Then
                strLabelFilename = QuoteTrim(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_COMPANY")))) & " - " & strTrackingNbr
            Else
                strLabelFilename = lngRow & " - " & strTrackingNbr
            End If
            strLabelFilename = Replace(strLabelFilename, "/", "_")
            strLabelFilename = Replace(strLabelFilename, "\", "_")
            strLabelFilename = Replace(strLabelFilename, ".", "_")
            strLabelFilename = strLabelFilename & "_CI.pdf"
            intFile = FreeFile
            Open g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename For Output As #intFile
            Print #intFile, strInsert
            Close #intFile
        End If ' CI
        
        Set xmlMail = New DOMDocument
        With xmlMail.appendChild(xmlMail.createElement("MailMessage"))
            .appendChild(xmlMail.createElement("To")).Text = "jfweir@hotmail.com"
            .appendChild(xmlMail.createElement("Subject")).Text = "FedEx shipment label attached"
            .appendChild(xmlMail.createElement("Body")).Text = "See FedEx tracking at" _
                & vbCrLf & "http://www.fedex.com/Tracking?tracknumber_list=" & strTrackingNbr
            .appendChild(xmlMail.createElement("Attachment")).Text = g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename
        End With
        If g_objSettings.Setting("MAIL_FOLDER") <> "" Then
            With g_objFSO.CreateTextFile(g_objSettings.Setting("MAIL_FOLDER") & strTrackingNbr & ".xml", True)
                .Write xmlMail.XML
                .Close
            End With
        End If
        
    End If
    Exit Function
    
ErrorExit:
    Err.Raise 12345, "FSMAPI", "Error during ShipOne, " & Err.Source & ", " & Err.Description
    Resume
    Resume Next
End Function
'



Public Function APIBatchSPODOne(lngRow As Long) As Boolean

    On Error GoTo ErrorExit
    
    Dim intFile As Long
    Dim strInsert As String
    Dim strDate As String
    Dim strTrans As String
    Dim strCurrentDate As String
    Dim objHyperlink As Hyperlink
    Dim aRange As Range
    
    'Set xmlDoc = MakeRoot("FDXSPODRequest")

    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    With xmlDoc.FirstChild
        With .appendChild(xmlDoc.createElement("RequestHeader"))
            .appendChild(xmlDoc.createElement("CustomerTransactionIdentifier")).Text = "CASPOD"
            .appendChild(xmlDoc.createElement("AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
            .appendChild(xmlDoc.createElement("MeterNumber")).Text = g_objSettings.Setting("METER_NUMBER")
            .appendChild(xmlDoc.createElement("CarrierCode")).Text = "FDXE"
        End With
'        If g_objSettings.Setting("SHEET_SHIPDATE") <> "" Then
'            .appendChild(xmlDoc.createElement("ShipDate")).Text = Format(CDate(mobjWorksheet.Cells(lngRow, lngShipDate)), "yyyy-mm-dd")
'        End If
        .appendChild(xmlDoc.createElement("ShipmentAccountNumber")).Text = mobjWorksheet.Cells(lngRow, SheetColumn("payr_cust_nbr"))
        .appendChild(xmlDoc.createElement("TrackingNumber")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber")))
        .appendChild(xmlDoc.createElement("LanguageCode")).Text = "EN"
        .appendChild(xmlDoc.createElement("ReturnLetterFormat")).Text = "PDF"
    End With

    g_strConfigHostEnvironment = g_objSettings.Setting("API_ENVIRONMENT")
    strTrans = SendRequestToGateway("<?xml version=""1.0"" encoding=""UTF-8"" ?> " & xmlDoc.FirstChild.XML)

    If 0 <> InStr(1, strTrans, "<Letter>") Then
        strTrans = Mid(strTrans, InStr(1, strTrans, "<Letter>"), Len(strTrans))
        strTrans = Left(strTrans, InStr(1, strTrans, "</Letter>") - 1)
        strTrans = Right(strTrans, Len(strTrans) - Len("<Letter>"))
        strInsert = DecodeStr64(strTrans)

        intFile = FreeFile
        Open mobjWorksheet.Parent.Path & "\" & mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) & ".pdf" For Output As #intFile
        Print #intFile, strInsert
        Close #intFile
        
        Call mobjWorksheet.Cells(lngRow, SheetColumn("POD_Location")).Hyperlinks.Delete
        Call mobjWorksheet.Cells(lngRow, SheetColumn("POD_Location")).Hyperlinks.Add( _
            mobjWorksheet.Cells(lngRow, SheetColumn("POD_Location")), _
            mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) & ".pdf")
        
    Else
        xmlReply.Save (mobjWorksheet.Parent.Path & "\" & mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) & ".xml")
        Call mobjWorksheet.Cells(lngRow, SheetColumn("POD_Location")).Hyperlinks.Delete
        Call mobjWorksheet.Cells(lngRow, SheetColumn("POD_Location")).Hyperlinks.Add( _
            mobjWorksheet.Cells(lngRow, SheetColumn("POD_Location")), _
            mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) & ".xml")
    End If
    
ExitHere:
    Exit Function

ErrorExit:
        mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) = Err.Source & " - " & Err.Description

End Function
'


Public Sub APIBatchStop()

    mblnStop = True

End Sub


Public Sub APIBatchTrack()

    Dim objWorkbook As Workbook
    Dim i As Long
    Dim lngShipTotal As Long
    Dim lngShipTry As Long
    
    mblnStop = False
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    Set objWorkbook = ActiveWorkbook
    Set mobjWorksheet = ActiveSheet
    
'    For Each objWorkbook In Workbooks
    
'        For Each mobjWorksheet In objWorkbook.Sheets
            
            Call APIBatchMPSforTracking
            
            i = CLng(g_objSettings.Setting("HEADER_ROW")) + 1
            Debug.Print mobjWorksheet.Name
            Do
                If Trim(mobjWorksheet.Cells(i, SheetColumn(g_objSettings.Setting("TrackingNumber")))) <> "" Then
                If IsNumeric(mobjWorksheet.Cells(i, SheetColumn(g_objSettings.Setting("TrackingNumber")))) Then
                    lngShipTry = lngShipTry + 1
                    If APIBatchTrackOne(i) Then
                        lngShipTotal = lngShipTotal + 1
                    Else
                    End If
                End If
                End If
                i = i + 1
                DoEvents
                If mblnStop Then Exit Do
                If i > 99 And i > lngShipTry * 1.2 Then Exit Do
                If i > 9999 Then Exit Do
            Loop
            
'           Exit For
'        Next mobjWorksheet
        
        ' Exit For
'    Next objWorkbook
    
    MsgBox "Batch complete." & vbCrLf _
        & lngShipTry & " records." & vbCrLf _
        & lngShipTotal & " SPOD."
    
End Sub


' 2013-02-15, JFW, Allow for comma delimited tracking numbers.
Public Function APIBatchTrackOne(lngRow As Long) As Boolean

    Dim obj As Object
    Dim xmlnode1 As IXMLDOMNode
    Dim xmlNode2 As IXMLDOMNode
    Dim xmlNode3 As IXMLDOMNode
    Dim xmlNode4 As IXMLDOMNode
    Dim xmlNode5 As IXMLDOMNode
    Dim xmlNode6 As IXMLDOMNode
    Dim xmlNode7 As IXMLDOMNode
    Dim strTrackingNumber() As String
    
    On Error GoTo ErrorExit
    
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    Set xmlDoc = New DOMDocument

    With xmlDoc.appendChild(xmlDoc.createElement("ns:TrackRequest"))
        Set obj = xmlDoc.createAttribute("xmlns:ns")
        obj.Text = "http://fedex.com/ws/track/v2"
        Call .Attributes.setNamedItem(obj)
        
        Set obj = xmlDoc.createAttribute("xmlns:xsi")
        obj.Text = "http://www.w3.org/2001/XMLSchema-instance"
        Call .Attributes.setNamedItem(obj)
        
        Set obj = xmlDoc.createAttribute("xsi:schemaLocation")
        obj.Text = "http://fedex.com/ws/track/v2/TrackService_v2.xsd"
        Call .Attributes.setNamedItem(obj)
        
        With .appendChild(xmlDoc.createElement("ns:WebAuthenticationDetail"))
            With .appendChild(xmlDoc.createElement("ns:UserCredential"))
                .appendChild(xmlDoc.createElement("ns:Key")).Text = g_objSettings.Setting("WS_KEY")
                If g_objSettings.Setting("WS_PASSWORD") <> "" Then
                    .appendChild(xmlDoc.createElement("ns:Password")).Text = g_objSettings.Setting("WS_PASSWORD")
                End If
            End With
        End With
        With .appendChild(xmlDoc.createElement("ns:ClientDetail"))
            .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
            .appendChild(xmlDoc.createElement("ns:MeterNumber")).Text = g_objSettings.Setting("METER_NUMBER")
        End With
        With .appendChild(xmlDoc.createElement("ns:TransactionDetail"))
            .appendChild(xmlDoc.createElement("ns:CustomerTransactionId")).Text = lngRow
        End With
        With .appendChild(xmlDoc.createElement("ns:Version"))
            .appendChild(xmlDoc.createElement("ns:ServiceId")).Text = "trck"
            .appendChild(xmlDoc.createElement("ns:Major")).Text = "2"
            .appendChild(xmlDoc.createElement("ns:Intermediate")).Text = "0"
            .appendChild(xmlDoc.createElement("ns:Minor")).Text = "0"
        End With
        With .appendChild(xmlDoc.createElement("ns:PackageIdentifier"))
            .appendChild(xmlDoc.createElement("ns:Value")).Text = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber")))
            .appendChild(xmlDoc.createElement("ns:Type")).Text = "TRACKING_NUMBER_OR_DOORTAG"
        End With
    End With

    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & lngRow & " TrackRequest.xml", True)
        .Write xmlDoc.FirstChild.XML
        .Close
    End With

    g_strConfigHostEnvironment = g_objSettings.Setting("API_ENVIRONMENT")
    strTrans = SendRequestToGateway(xmlDoc.FirstChild.XML)

    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & lngRow & " TrackReply.xml", True)
        .Write strTrans
        .Close
    End With
    Set xmlReply = New DOMDocument
    xmlReply.loadXML strTrans

    mobjWorksheet.Cells(lngRow, SheetColumn("TrackingStatus")).Font.ColorIndex = 1
    mobjWorksheet.Cells(lngRow, SheetColumn("TrackingStatus")) = ""
    
    If xmlReply.selectSingleNode("v2:TrackReply") Is Nothing Then
        mobjWorksheet.Cells(lngRow, SheetColumn("TrackingStatus")) = "Unknown reply:  " & strTrans
        mobjWorksheet.Cells(lngRow, SheetColumn("TrackingStatus")).Font.ColorIndex = 3
    End If

    mobjWorksheet.Cells(lngRow, SheetColumn("TimeStamp")) = Now()

    For Each xmlnode1 In xmlReply.selectNodes("v2:TrackReply")
        For Each xmlNode2 In xmlnode1.selectNodes("v2:Notifications")
            If UCase(xmlNode2.selectSingleNode("v2:Severity").Text) <> "SUCCESS" Then
                For Each xmlNode3 In xmlNode2.selectNodes("v2:LocalizedMessage")
                    If mobjWorksheet.Cells(lngRow, SheetColumn("TrackingStatus")) = "" Then
                        mobjWorksheet.Cells(lngRow, SheetColumn("TrackingStatus")) = "Errors or warnings:  "
                    End If
                    mobjWorksheet.Cells(lngRow, SheetColumn("TrackingStatus")) = mobjWorksheet.Cells(lngRow, SheetColumn("TrackingStatus")) _
                        & vbLf & xmlNode3.Text
                    mobjWorksheet.Cells(lngRow, SheetColumn("TrackingStatus")).Font.ColorIndex = 3
                    mobjWorksheet.Cells(lngRow, SheetColumn("TrackingStatus")).WrapText = 0
                Next
            Else
                mobjWorksheet.Cells(lngRow, SheetColumn("TrackingStatus")) = ""
                mobjWorksheet.Cells(lngRow, SheetColumn("TrackingStatus")).Font.ColorIndex = 1
            End If
        Next
        
        For Each xmlNode2 In xmlnode1.selectNodes("v2:TrackDetails")
            For Each xmlNode3 In xmlNode2.selectNodes("v2:ActualDeliveryTimestamp")
                mobjWorksheet.Cells(lngRow, SheetColumn("DeliveryTimestamp")) = xmlNode3.Text
            Next
            For Each xmlNode3 In xmlNode2.selectNodes("v2:DeliverySignatureName")
                mobjWorksheet.Cells(lngRow, SheetColumn("DeliverySignatureName")) = xmlNode3.Text
            Next
            For Each xmlNode4 In xmlNode2.selectNodes("v2:Events")
                For Each xmlNode5 In xmlNode4.selectNodes("v2:EventDescription")
                    mobjWorksheet.Cells(lngRow, SheetColumn("DeliveryStatus")) = xmlNode5.Text
                Next
                For Each xmlNode5 In xmlNode4.selectNodes("v2:Timestamp")
                    mobjWorksheet.Cells(lngRow, SheetColumn("DeliveryTimestamp")) = xmlNode5.Text
                Next
            Next
            ' First event only
            Exit For
        Next
    Next
        
    
ExitHere:
    Exit Function

ErrorExit:
    mobjWorksheet.Cells(lngRow, SheetColumn("TrackingStatus")) = Err.Source & " - " & Err.Description
    mobjWorksheet.Cells(lngRow, SheetColumn("TrackingStatus")).WrapText = 0
    
End Function
'



Public Sub APIDelete()
    
    Dim i As Long, j As Long
    Dim strTrackNo As String
    Dim intFile As Long
    
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    For i = Selection.Column To (Selection.Column + Selection.Columns.Count - 1)

        If ActiveSheet.Cells(g_objSettings.Setting("HEADER_ROW"), i) = g_objSettings.Setting("TrackingNumber") Then

            Set mobjWorksheet = ActiveSheet
            
            For j = Selection.Row To (Selection.Row + Selection.Rows.Count - 1)

                If ActiveSheet.Cells(j, i) <> "" Then
                    strTrackNo = ActiveSheet.Cells(j, i)
                    Debug.Print "Delete " & strTrackNo
                    
                    If IsNumeric(strTrackNo) Then
                        If APIDeleteOne(strTrackNo, j) Then
                            ActiveSheet.Cells(j, i) = ""
                        End If
                    End If

                End If

            Next j

        End If

    Next i


End Sub


Public Function APIDeleteOne(strTrack As String, Optional lngRow As Long = 0) As Boolean

    On Error GoTo ErrorExit
    
    Dim intFile As Long
    Dim strInsert As String

    Dim strTrans As String
    Dim obj As Object
    Dim objFile As File
    
    Dim xmlnode1 As IXMLDOMNode
    Dim xmlNode2 As IXMLDOMNode
    Dim xmlNode3 As IXMLDOMNode
    Dim xmlNode4 As IXMLDOMNode
    Dim xmlNode5 As IXMLDOMNode
    Dim xmlNode6 As IXMLDOMNode
    Dim xmlNode7 As IXMLDOMNode
    
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    Set xmlDoc = New DOMDocument

    With xmlDoc.appendChild(xmlDoc.createElement("ns:DeleteShipmentRequest"))
        Set obj = xmlDoc.createAttribute("xmlns:ns")
        obj.Text = "http://fedex.com/ws/ship/v3"
        Call .Attributes.setNamedItem(obj)
        
        Set obj = xmlDoc.createAttribute("xmlns:xsi")
        obj.Text = "http://www.w3.org/2001/XMLSchema-instance"
        Call .Attributes.setNamedItem(obj)
        
        Set obj = xmlDoc.createAttribute("xsi:schemaLocation")
        obj.Text = "http://fedex.com/ws/ship/v3/ShipService_v3.xsd"
        Call .Attributes.setNamedItem(obj)
        
        With .appendChild(xmlDoc.createElement("ns:WebAuthenticationDetail"))
            
            ' .appendChild(xmlDoc.createElement("ns:UserCredential")).Text = "u4QZv4M2irse5XXt"
            
            With .appendChild(xmlDoc.createElement("ns:UserCredential"))
                .appendChild(xmlDoc.createElement("ns:Key")).Text = g_objSettings.Setting("WS_KEY")
                If g_objSettings.Setting("WS_PASSWORD") <> "" Then
                    .appendChild(xmlDoc.createElement("ns:Password")).Text = g_objSettings.Setting("WS_PASSWORD")
                End If
            End With
            
        End With
        With .appendChild(xmlDoc.createElement("ns:ClientDetail"))
            .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
            .appendChild(xmlDoc.createElement("ns:MeterNumber")).Text = g_objSettings.Setting("METER_NUMBER")
        End With
        With .appendChild(xmlDoc.createElement("ns:TransactionDetail"))
            .appendChild(xmlDoc.createElement("ns:CustomerTransactionId")).Text = strTrack
        End With
        With .appendChild(xmlDoc.createElement("ns:Version"))
            .appendChild(xmlDoc.createElement("ns:ServiceId")).Text = "ship"
            .appendChild(xmlDoc.createElement("ns:Major")).Text = "3"
            .appendChild(xmlDoc.createElement("ns:Intermediate")).Text = "0"
            .appendChild(xmlDoc.createElement("ns:Minor")).Text = "0"
        End With
        .appendChild(xmlDoc.createElement("ns:TrackingNumber")).Text = strTrack
        .appendChild(xmlDoc.createElement("ns:DeletionControl")).Text = "DELETE_ONE_PACKAGE"
    End With

    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & strTrack & " Delete.xml", True)
        .Write xmlDoc.FirstChild.XML
        .Close
    End With

    g_strConfigHostEnvironment = g_objSettings.Setting("API_ENVIRONMENT")
    strTrans = SendRequestToGateway(xmlDoc.FirstChild.XML)

    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & strTrack & " Delete Reply.xml", True)
        .Write strTrans
        .Close
    End With
    Set xmlReply = New DOMDocument
    xmlReply.loadXML strTrans

    APIDeleteOne = True

    If lngRow > 0 Then
        If xmlReply.selectSingleNode("v3:ShipmentReply") Is Nothing Then
            mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))).Font.ColorIndex = 3
            APIDeleteOne = False
        End If
        
        ' 2011-11-25, JFW, blank notifications
        If SheetColumnExists("Notifications") Then
            If mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) <> "" Then
                mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = ""
            End If
        End If
        
        For Each xmlnode1 In xmlReply.selectNodes("v3:ShipmentReply")
            For Each xmlNode2 In xmlnode1.selectNodes("v3:Notifications")
                For Each xmlNode3 In xmlNode2.selectNodes("v3:LocalizedMessage")
                    If xmlNode3.Text <> "Success" Then
                        
                    If 0 = InStr(1, xmlNode3.Text, "already in a deleted state") Then
                        
                        
                        If mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = "" Then
                            mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = "Errors or warnings:  "
                        End If
                        mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")) = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))) _
                            & vbLf & xmlNode3.Text
                        mobjWorksheet.Cells(lngRow, SheetColumn("Notifications")).Font.ColorIndex = 3
                        
                        mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber"))).Font.ColorIndex = 3
                        APIDeleteOne = False
                    End If ' already deleted
                    End If
                Next
            Next
        Next
    End If
    
    For Each objFile In g_objFSO.GetFolder(g_objSettings.Setting("LABEL_FOLDER")).Files
        If 0 <> InStr(objFile.Name, strTrack) Then
            If g_objFSO.FileExists(objFile.Path & "_Delete") Then
                g_objFSO.DeleteFile objFile.Path & "_Delete", True
            End If
            g_objFSO.MoveFile objFile.Path, objFile.Path & "_Delete"
        End If
    Next objFile
    
    If g_objSettings.Setting("MAIL_FOLDER") <> "" Then
        For Each objFile In g_objFSO.GetFolder(g_objSettings.Setting("MAIL_FOLDER")).Files
            If 0 <> InStr(objFile.Name, strTrack) Then
                g_objFSO.MoveFile objFile.Path, objFile.Path & "_Delete"
            End If
        Next objFile
    End If
    
    Exit Function

ErrorExit:
    Err.Raise 12345, "FSMAPI", "Error during BatchShipCA, " & Err.Source & ", " & Err.Description
    Resume
End Function


Public Sub APIReprintLabels()

    Dim objWorkbook As Workbook
    Dim i As Long, j As Long
    Dim lngShipTotal As Long
    Dim lngShipTry As Long
    Dim strShipDate As String
    Dim boolShipIt As Boolean
    
    mblnStop = False
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    ' Reprint by date
'    mdtShipDate = #11/8/2008#
'    ' If shipper has ship date then must confirm
'    If g_objSettings.Setting("SHEET_SHIPDATE") <> "" Then
'        While mdtShipDate < Date
'            strShipDate = InputBox("Please confirm the DATE of labels you want to print", "API Batch date", Date)
'            If strShipDate = "" Then Exit Sub
'            If IsDate(strShipDate) Then mdtShipDate = CDate(strShipDate)
'        Wend
'    Else
'        mdtShipDate = Date
'    End If
'
'    Set objWorkbook = ActiveWorkbook
'    Set mobjWorksheet = ActiveSheet
'
'    i = CLng(g_objSettings.Setting("HEADER_ROW")) + 1
'    Debug.Print mobjWorksheet.Name
'    Do
'        If SheetColumn(g_objSettings.Setting("SHEET_CONTACT")) < 1 Then
'            Exit Do
'        End If
'        If mobjWorksheet.Cells(i, SheetColumn(g_objSettings.Setting("SHEET_CONTACT"))) = "" Then
'            Exit Do
'        End If
'        If mobjWorksheet.Cells(i, SheetColumn(g_objSettings.Setting("TrackingNumber"))) <> "" Then
'            boolShipDate = False
'            If g_objSettings.Setting("SHEET_SHIPDATE") = "" Then
'                boolShipDate = True
'            ElseIf IsDate(mobjWorksheet.Cells(i, lngshipdate)) Then
'                If CDate(mobjWorksheet.Cells(i, lngshipdate)) = mdtShipDate Then
'                    boolShipDate = True
'                End If
'            End If
'            If boolShipDate Then
'
'                ' New MPS processing
'                Debug.Print mobjWorksheet.Cells(i, 1)
'                lngShipTry = lngShipTry + 1
'
'                If APIReprintOne(i) Then
'                    lngShipTotal = lngShipTotal + 1
'                End If
'
'            End If
'        End If
'        i = i + 1
'        DoEvents
'        If mblnStop Then Exit Do
'    Loop
    
    ' Reprint selected tracking numbers
    For i = Selection.Column To (Selection.Column + Selection.Columns.Count - 1)
        If ActiveSheet.Cells(g_objSettings.Setting("HEADER_ROW"), i) = "TrackingNumber" Then
            Set mobjWorksheet = ActiveSheet
            For j = Selection.Row To (Selection.Row + Selection.Rows.Count - 1)
                If ActiveSheet.Cells(j, i) <> "" Then
                    strTrackingNbr = ActiveSheet.Cells(j, i)
                    Debug.Print "Reprint " & strTrackingNbr
                    If IsNumeric(strTrackingNbr) Then
                        If Len(strTrackingNbr) > 10 Then
                            If APIReprintOne(j) Then
                                lngShipTotal = lngShipTotal + 1
                            End If ' reprinted
                        End If ' length
                    End If ' numeric
                End If ' not blank
            Next j ' each selected row
        End If ' TrackingNumber column
    Next i ' each selected column

    
    MsgBox "Reprint complete.  " _
        & lngShipTotal & " labels."
    Exit Sub
    
End Sub


Public Function APIReprintOne(lngRow As Long) As Boolean

    On Error GoTo ErrorExit
    
    Dim objFile As File
    
    Dim intFile As Long
    Dim strInsert As String

    
    Dim i As Long
    Dim strCountry As String
    Dim strCI As String
    Dim strLabel As String
    Dim strReply As String
    Dim strMasterAwb, strFormId, strPackageCnt As String
    Dim strOption() As String
    Dim boolEmail As Boolean
    Dim boolCommodity As Boolean
    Dim lngCommodity As Long
    Dim strCommodity() As String
    Dim strCommodityList() As String
    Dim dblCustomsValue As Double
    Dim dblCommodityWeight As Double
    Dim dblCommodityCount As Double
    Dim obj As Object
    Dim xmlnode1 As IXMLDOMNode
    Dim xmlNode2 As IXMLDOMNode
    Dim xmlNode3 As IXMLDOMNode
    Dim xmlNode4 As IXMLDOMNode
    Dim xmlNode5 As IXMLDOMNode
    Dim xmlNode6 As IXMLDOMNode
    Dim xmlNode7 As IXMLDOMNode
    
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    strTrackingNbr = mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("TrackingNumber")))

    If Len(Trim(strTrackingNbr)) > 10 Then
        
        For Each objFile In g_objFSO.GetFolder(g_objSettings.Setting("LABEL_FOLDER")).Files
            If 0 <> InStr(1, objFile.Name, strTrackingNbr) Then
                strLabelFilename = objFile.Name
                Exit For
            End If
        Next objFile
        
        If strLabelFilename <> "" Then
            If g_objSettings.Setting("PRINTER") <> "" Then
                If g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PDF" Then
                    Shell """" & g_objSettings.SettingWithDefault("AdobeReaderPath", "AcroRd32.exe") & """" & " /t" _
                        & " " & """" & g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename & """" _
                        & " " & """" & g_objSettings.Setting("PRINTER") & """" & "", vbNormalFocus
                ElseIf g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PNG" Then
                    Shell """C:\Program Files\Internet Explorer\iexplore.exe"" """ & g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename & """", vbNormalFocus
                Else
                    g_objFSO.CopyFile g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename, g_objSettings.Setting("PRINTER")
                End If
                APIReprintOne = True
            End If
        End If
        
    End If
    Exit Function
    
ErrorExit:
    Err.Raise 12345, "FSMAPI", "Error during ShipOne, " & Err.Source & ", " & Err.Description
    Resume
    Resume Next
End Function



Public Function APIUploadImage() As Boolean

    Dim intFile As Long
    Dim lngImageNumber As Long
    Dim obj As Object
    Dim strError As String
    Dim strFilename As String
    Dim strFileContent As String
    Dim objFileDialog As FileDialog
    
    Dim objBase64 As New classBase64
    Dim objBase64B As New classBase64B
    
    Dim xmlnode1 As IXMLDOMNode
    Dim xmlNode2 As IXMLDOMNode
    Dim xmlNode3 As IXMLDOMNode
    Dim xmlNode4 As IXMLDOMNode
    Dim xmlNode5 As IXMLDOMNode
    Dim xmlNode6 As IXMLDOMNode
    Dim xmlNode7 As IXMLDOMNode
    
    On Error GoTo ErrorExit
    
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    Set objFileDialog = Application.FileDialog(msoFileDialogOpen)
    objFileDialog.AllowMultiSelect = False
    objFileDialog.Title = "Choose image file"
    If objFileDialog.Show Then
        If objFileDialog.SelectedItems.Count < 1 Then Exit Function
        strFilename = objFileDialog.SelectedItems.Item(1)
        strFileContent = g_objFSO.GetFile(strFilename).OpenAsTextStream.ReadAll
    End If
    
    Do
        If lngImageNumber >= 1 And lngImageNumber <= 5 Then Exit Do
        lngImageNumber = CLng(InputBox("Enter the image number, 1 to 5, zero to exit", , 0))
        If lngImageNumber = 0 Then Exit Function
    Loop
    
    Set xmlDoc = New DOMDocument

    With xmlDoc.appendChild(xmlDoc.createElement("ns:UploadImagesRequest"))
        
        
        Set obj = xmlDoc.createAttribute("xmlns:ns")
        obj.Text = "http://fedex.com/ws/uploaddocument/v1"
        Call .Attributes.setNamedItem(obj)
        
        Set obj = xmlDoc.createAttribute("xmlns:xsi")
        obj.Text = "http://www.w3.org/2001/XMLSchema-instance"
        Call .Attributes.setNamedItem(obj)
        
'        Set obj = xmlDoc.createAttribute("xsi:schemaLocation")
'        obj.Text = "http://fedex.com/ws/ship/v3/ShipService_v3.xsd"
'        Call .Attributes.setNamedItem(obj)

        With .appendChild(xmlDoc.createElement("ns:WebAuthenticationDetail"))
            With .appendChild(xmlDoc.createElement("ns:UserCredential"))
                .appendChild(xmlDoc.createElement("ns:Key")).Text = g_objSettings.Setting("WS_KEY")
                If g_objSettings.Setting("WS_PASSWORD") <> "" Then
                    .appendChild(xmlDoc.createElement("ns:Password")).Text = g_objSettings.Setting("WS_PASSWORD")
                End If
            End With
        End With
        With .appendChild(xmlDoc.createElement("ns:ClientDetail"))
            .appendChild(xmlDoc.createElement("ns:AccountNumber")).Text = g_objSettings.Setting("ACCOUNT_NUMBER")
            .appendChild(xmlDoc.createElement("ns:MeterNumber")).Text = g_objSettings.Setting("METER_NUMBER")
        End With
        With .appendChild(xmlDoc.createElement("ns:TransactionDetail"))
            .appendChild(xmlDoc.createElement("ns:CustomerTransactionId")).Text = "UploadImage"
        End With
        With .appendChild(xmlDoc.createElement("ns:Version"))
            .appendChild(xmlDoc.createElement("ns:ServiceId")).Text = "cdus"
            .appendChild(xmlDoc.createElement("ns:Major")).Text = "1"
            .appendChild(xmlDoc.createElement("ns:Intermediate")).Text = "1"
            .appendChild(xmlDoc.createElement("ns:Minor")).Text = "0"
        End With
        With .appendChild(xmlDoc.createElement("ns:Images"))
            .appendChild(xmlDoc.createElement("ns:Id")).Text = "IMAGE_" & lngImageNumber
            
            ' Works
            ' File from VB.Net xml
            ' .appendChild(xmlDoc.createElement("ns:Image")).Text = g_objFSO.GetFile("C:\ImageBase64.txt").OpenAsTextStream.ReadAll
            
            '+
            ' Using modBase64
            ' Not giving a valid image upload
'            .appendChild(xmlDoc.createElement("ns:Image")).Text = EncodeStr64(strFileContent)
'            intFile = FreeFile
'            Open g_objSettings.Setting("LABEL_FOLDER") & "test.jpg" For Output As #intFile
'            Print #intFile, DecodeStr64(EncodeStr64(strFileContent))
'            Close #intFile
            '~
            
            '+
            ' Using classBase64
            ' Not working
'            objBase64.Value = strFileContent
'            .appendChild(xmlDoc.createElement("ns:Image")).Text = objBase64.encode
'
'            strFileContent = objBase64.encode
'            objBase64.Value = strFileContent
'            strFileContent = objBase64.decode
'
'            intFile = FreeFile
'            Open g_objSettings.Setting("LABEL_FOLDER") & "test.jpg" For Output As #intFile
'            Print #intFile, strFileContent
'            Close #intFile
            '~
            
            '+
            ' Using classBase64B
            ' Not working
'            .appendChild(xmlDoc.createElement("ns:Image")).Text = objBase64B.encode(strFileContent)
'
'            strFileContent = objBase64B.encode(strFileContent)
'            strFileContent = objBase64B.decode(strFileContent)
'
'            intFile = FreeFile
'            Open g_objSettings.Setting("LABEL_FOLDER") & "test.jpg" For Output As #intFile
'            Print #intFile, strFileContent
'            Close #intFile
            '~
            
            '+
            ' Using Module1
            ' Not working
            .appendChild(xmlDoc.createElement("ns:Image")).Text = EncodeFile(strFilename)
            
            strFileContent = EncodeFile(strFilename)

            intFile = FreeFile
            Open g_objSettings.Setting("LABEL_FOLDER") & "test.jpg" For Output As #intFile
            Print #intFile, strFileContent
            Close #intFile
            '~
            
        End With
    End With

    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & "UploadImageRequest.xml", True)
        .Write xmlDoc.FirstChild.XML
        .Close
    End With

    g_strConfigHostEnvironment = g_objSettings.Setting("API_ENVIRONMENT")
    strTrans = SendRequestToGateway(xmlDoc.FirstChild.XML)

    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & "UploadImageReply.xml", True)
        .Write strTrans
        .Close
    End With
    Set xmlReply = New DOMDocument
    xmlReply.loadXML strTrans

    For Each xmlnode1 In xmlReply.selectNodes("v1:UploadImagesReply")
        For Each xmlNode2 In xmlnode1.selectNodes("v1:Notifications")
            If UCase(xmlNode2.selectSingleNode("v1:Severity").Text) <> "SUCCESS" Then
                For Each xmlNode3 In xmlNode2.selectNodes("v1:LocalizedMessage")
                    If strError = "" Then strError = "Errors or warnings:  "
                    strError = strError & vbLf & xmlNode3.Text
                Next
                MsgBox strError
            End If
        Next

        For Each xmlNode2 In xmlnode1.selectNodes("v1:ImageStatuses")
            MsgBox xmlNode2.selectSingleNode("v1:Id").Text & " " & xmlNode2.selectSingleNode("v1:Status").Text
            Exit For
        Next
    Next
        
    
ExitHere:
    Exit Function

ErrorExit:
    MsgBox "Error in APIUploadImage, " & Err.Source & ", " & Err.Description
    
End Function
'

Public Function GetCurrency() As String
    If g_objSettings.Setting("SENDER_COUNTRY") = "US" Then
        GetCurrency = "USD"
    Else
        GetCurrency = "CAD"
    End If
End Function
'

Public Function OptionIndex(strArray() As String, strItem As String) As Long

    Dim i As Integer
    For i = 0 To UBound(strArray)
        If 0 <> InStr(1, strArray(i), UCase(strItem)) Then
            OptionIndex = i
            Exit Function
        End If
    Next i
    OptionIndex = -1

End Function

Public Function OptionSplit(strText As String) As String()

    Dim i As Integer
    Dim str() As String
    If strText = "" Then
        ReDim str(0)
    Else
        str = Split(UCase(strText), " ")
    End If
    For i = 0 To UBound(str)
        str(i) = Trim(str(i))
    Next i
    OptionSplit = str
    
End Function


Public Function OptionTest(str1 As String, Optional str2 As String = "", Optional str3 As String = "") As Boolean

    Dim i As Integer
    Dim j As Integer
    If Join(strOption(), "") = "" Then
        ReDim strOption(0)
    End If
    For i = LBound(strOption) To UBound(strOption)
        If 0 <> InStr(1, strOption(i), UCase(str1)) Then
            If str2 <> "" Then
                If i < UBound(strOption) Then
                    If 0 <> InStr(1, strOption(i + 1), UCase(str2)) Then
                        OptionTest = True
                        Exit For
                    End If
                End If
            Else
                OptionTest = True
                Exit For
            End If
            Exit Function
        End If
    Next i

End Function

Private Function PackageType(lngRow As Long) As String

    If SheetColumn(g_objSettings.Setting("SHEET_PACKAGETYPE"), True) > 0 Then
        If 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_PACKAGETYPE")))), "env") Then
            PackageType = "FEDEX_ENVELOPE"
        ElseIf 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_PACKAGETYPE")))), "box") Then
            PackageType = "FEDEX_BOX"
        ElseIf 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_PACKAGETYPE")))), "pak") Then
            PackageType = "FEDEX_PAK"
        ElseIf 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_PACKAGETYPE")))), "tube") Then
            PackageType = "FEDEX_TUBE"
        Else
            PackageType = "YOUR_PACKAGING"
        End If
    ElseIf g_objSettings.Setting("SHEET_PACKAGETYPE") <> "" Then
        If 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_PACKAGETYPE")), "YOUR") Then
            PackageType = "YOUR_PACKAGING"
        ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_PACKAGETYPE")), "OWN") Then
            PackageType = "YOUR_PACKAGING"
        ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_PACKAGETYPE")), "ENV") Then
            PackageType = "FEDEX_ENVELOPE"
        ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_PACKAGETYPE")), "BOX") Then
            PackageType = "FEDEX_BOX"
        ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_PACKAGETYPE")), "PAK") Then
            PackageType = "FEDEX_PAK"
        ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_PACKAGETYPE")), "TUBE") Then
            PackageType = "FEDEX_TUBE"
        Else
            PackageType = "YOUR_PACKAGING"
        End If
    Else
        PackageType = "YOUR_PACKAGING"
    End If

End Function



Private Function ServiceType(lngRow As Long, ByVal strCountry As String) As String

    If strCountry = "" Then strCountry = g_objSettings.Setting("SENDER_COUNTRY")
    
    If SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE"), True) > 0 Then
        If 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE")))), "econ") _
        And 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE")))), "freight") Then
            If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                ServiceType = "INTERNATIONAL_ECONOMY_FREIGHT"
            Else
                ServiceType = "FEDEX_2_DAY_FREIGHT"
            End If
        ElseIf 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE")))), "priority") Then
            If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                ServiceType = "INTERNATIONAL_PRIORITY"
            Else
                ServiceType = "PRIORITY_OVERNIGHT"
            End If
        ElseIf 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE")))), "po") Then
            If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                ServiceType = "INTERNATIONAL_PRIORITY"
            Else
                ServiceType = "PRIORITY_OVERNIGHT"
            End If
        ElseIf 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE")))), "econ") Then
            If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                ServiceType = "INTERNATIONAL_ECONOMY"
            Else
                ServiceType = "FEDEX_2_DAY"
            End If
        ElseIf 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE")))), "2") Then
            If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                ServiceType = "INTERNATIONAL_ECONOMY"
            Else
                ServiceType = "FEDEX_2_DAY"
            End If
        ElseIf 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE")))), "two") Then
            If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                ServiceType = "INTERNATIONAL_ECONOMY"
            Else
                ServiceType = "FEDEX_2_DAY"
            End If
        ElseIf 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE")))), "saver") Then
            If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                ServiceType = "INTERNATIONAL_ECONOMY"
            Else
                ServiceType = "FEDEX_EXPRESS_SAVER"
            End If
        ElseIf 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE")))), "freight") Then
            If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                ServiceType = "INTERNATIONAL_PRIORITY_FREIGHT"
            Else
                ServiceType = "FEDEX_1_DAY_FREIGHT"
            End If
        ElseIf 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE")))), "first") Then
            If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                ServiceType = "INTERNATIONAL_FIRST"
            Else
                ServiceType = "FIRST_OVERNIGHT"
            End If
        ElseIf 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE")))), "ground") Then
            ServiceType = "FEDEX_GROUND"
        ElseIf 0 <> InStr(1, LCase(mobjWorksheet.Cells(lngRow, SheetColumn(g_objSettings.Setting("SHEET_SERVICETYPE")))), "home") Then
            ServiceType = "GROUND_HOME_DELIVERY"
        Else
            If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
                If g_objSettings.Setting("DEFAULT_INTL_SERVICETYPE") = "" Then
                    g_objSettings.Setting("DEFAULT_INTL_SERVICETYPE") = "INTERNATIONAL_PRIORITY"
                    MsgBox "See settings, DEFAULT_INTL_SERVICETYPE, set to " & "INTERNATIONAL_PRIORITY", vbApplicationModal
                    g_objSettings.SaveSettings
                End If
                ServiceType = g_objSettings.Setting("DEFAULT_INTL_SERVICETYPE")
            Else
                If g_objSettings.Setting("DEFAULT_DOM_SERVICETYPE") = "" Then
                    g_objSettings.Setting("DEFAULT_DOM_SERVICETYPE") = "PRIORITY_OVERNIGHT"
                    MsgBox "See settings, DEFAULT_DOM_SERVICETYPE, set to " & "PRIORITY_OVERNIGHT", vbApplicationModal
                    g_objSettings.SaveSettings
                End If
                ServiceType = g_objSettings.Setting("DEFAULT_DOM_SERVICETYPE")
            End If
        End If
    ElseIf g_objSettings.Setting("SHEET_SERVICETYPE") <> "" Then
        If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
            If 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "PRIORITY") Then
                ServiceType = "INTERNATIONAL_PRIORITY"
            ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "PO") Then
                ServiceType = "INTERNATIONAL_PRIORITY"
            ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "ECON") Then
                ServiceType = "INTERNATIONAL_ECONOMY"
            ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "2") Then
                ServiceType = "INTERNATIONAL_ECONOMY"
            ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "TWO") Then
                ServiceType = "INTERNATIONAL_ECONOMY"
            ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "FIRST") Then
                ServiceType = "INTERNATIONAL_FIRST"
            ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "SAVER") Then
                ServiceType = "FEDEX_EXPRESS_SAVER"
            ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "GROUND") Then
                ServiceType = "FEDEX_GROUND"
            Else
                If g_objSettings.Setting("DEFAULT_INTL_SERVICETYPE") = "" Then
                    g_objSettings.Setting("DEFAULT_INTL_SERVICETYPE") = "INTERNATIONAL_PRIORITY"
                    MsgBox "See settings, DEFAULT_INTL_SERVICETYPE, set to " & "INTERNATIONAL_PRIORITY", vbApplicationModal
                    g_objSettings.SaveSettings
                End If
                ServiceType = g_objSettings.Setting("DEFAULT_INTL_SERVICETYPE")
            End If
        Else
            If 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "PRIORITY") Then
                ServiceType = "PRIORITY_OVERNIGHT"
            ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "PO") Then
                ServiceType = "PRIORITY_OVERNIGHT"
            ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "ECON") Then
                ServiceType = "FEDEX_2_DAY"
            ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "2") Then
                ServiceType = "FEDEX_2_DAY"
            ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "TWO") Then
                ServiceType = "FEDEX_2_DAY"
            ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "FIRST") Then
                ServiceType = "FIRST_OVERNIGHT"
            ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "SAVER") Then
                ServiceType = "FEDEX_EXPRESS_SAVER"
            ElseIf 0 <> InStr(1, UCase(g_objSettings.Setting("SHEET_SERVICETYPE")), "GROUND") Then
                ServiceType = "FEDEX_GROUND"
            Else
                If g_objSettings.Setting("DEFAULT_DOM_SERVICETYPE") = "" Then
                    g_objSettings.Setting("DEFAULT_DOM_SERVICETYPE") = "PRIORITY_OVERNIGHT"
                    MsgBox "See settings, DEFAULT_DOM_SERVICETYPE, set to " & "PRIORITY_OVERNIGHT", vbApplicationModal
                    g_objSettings.SaveSettings
                End If
                ServiceType = g_objSettings.Setting("DEFAULT_DOM_SERVICETYPE")
            End If
        End If
    Else
        If strCountry <> g_objSettings.Setting("SENDER_COUNTRY") Then
            If g_objSettings.Setting("DEFAULT_INTL_SERVICETYPE") = "" Then
                    g_objSettings.Setting("DEFAULT_INTL_SERVICETYPE") = "INTERNATIONAL_PRIORITY"
                    MsgBox "See settings, DEFAULT_INTL_SERVICETYPE, set to " & "INTERNATIONAL_PRIORITY", vbApplicationModal
                    g_objSettings.SaveSettings
            End If
            ServiceType = g_objSettings.Setting("DEFAULT_INTL_SERVICETYPE")
        Else
            If g_objSettings.Setting("DEFAULT_DOM_SERVICETYPE") = "" Then
                    g_objSettings.Setting("DEFAULT_DOM_SERVICETYPE") = "PRIORITY_OVERNIGHT"
                    MsgBox "See settings, DEFAULT_DOM_SERVICETYPE, set to " & "PRIORITY_OVERNIGHT", vbApplicationModal
                    g_objSettings.SaveSettings
            End If
            ServiceType = g_objSettings.Setting("DEFAULT_DOM_SERVICETYPE")
        End If
    End If

    Debug.Print lngRow, ServiceType
    
End Function

'
' Return the Excel column number of a title, from the "HEADER_ROW".
' New parameter that column is being tested only, and not to auto create the column.
'
Private Function SheetColumn(strColumn As String, Optional boolTestOnly As Boolean = False) As Long

    Dim i As Long
    If strColumn <> "" Then
        For i = 1 To 999
            If mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), i) = "" Then
                Exit For
            End If
            If UCase(mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), i)) = UCase(strColumn) Then
                SheetColumn = i
                Exit For
            End If
        Next i
        If SheetColumn = 0 Then
            For i = 1 To 999
                If mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), i) = "" Then Exit For
                If 0 <> InStr(1, UCase(mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), i)), UCase(strColumn)) Then
                    SheetColumn = i
                    Exit For
                End If
            Next i
        End If
        If SheetColumn = 0 And Not boolTestOnly Then
            mobjWorksheet.Columns(i).Insert
            mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), i) = strColumn
            SheetColumn = i
        End If
    End If
    
End Function
'


Private Function SheetColumnExists(strColumn As String) As Boolean

    Dim i As Long
    For i = 1 To 999
        If mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), i) = "" Then Exit For
        If UCase(mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), i)) = UCase(strColumn) Then
            SheetColumnExists = True
            Exit For
        End If
    Next i
    If Not SheetColumnExists Then
        For i = 1 To 999
            If mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), i) = "" Then Exit For
            If 0 <> InStr(1, UCase(mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), i)), UCase(strColumn)) Then
                SheetColumnExists = True
                Exit For
            End If
        Next i
    End If
    
End Function
'


Private Sub DeleteColumn(strColumn As String)   ' //zp new
    Dim i, colToDelete As Long
    colToDelete = 0
    For i = 1 To 999
        If mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), i) = "" Then Exit For
        If UCase(mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), i)) = UCase(strColumn) Then
            colToDelete = i
            Exit For
        End If
    Next i
    If colToDelete = 0 Then
        For i = 1 To 999
            If mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), i) = "" Then Exit For
            If 0 <> InStr(1, UCase(mobjWorksheet.Cells(g_objSettings.Setting("HEADER_ROW"), i)), UCase(strColumn)) Then
                colToDelete = i
                Exit For
            End If
        Next i
    End If
    If colToDelete <> 0 Then
        mobjWorksheet.Columns(colToDelete).Delete
    End If
    
End Sub

'
'Public Sub TestSOAP()
'
'
'    Dim intFile As Long
'    Dim strInsert As String
'
'    Dim i As Long
'    Dim strCountry As String
'    Dim strTrans As String
'    Dim strMasterAwb, strFormId, strPackageCnt As String
'    Dim strOption() As String
'    Dim strCommodity() As String
'    Dim obj As Object
'    Dim xmlnode1 As IXMLDOMNode
'    Dim xmlNode2 As IXMLDOMNode
'    Dim xmlNode3 As IXMLDOMNode
'    Dim xmlNode4 As IXMLDOMNode
'    Dim xmlNode5 As IXMLDOMNode
'    Dim xmlNode6 As IXMLDOMNode
'    Dim xmlNode7 As IXMLDOMNode
'
'    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
'    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
'
'
'
'
'
'Dim xmlString As String
'Dim DDate As Date
'Dim SDate As String
'SDate = Format(Now, "yyyy-mm-dd") & "T" & "23:00:00-11:00"
'xmlString = ""
'xmlString = xmlString & "<soapenv:Envelope xmlns:soapenv=""http://schemas.xmlsoap.org/soap/envelope/"" xmlns:v10=""http://fedex.com/ws/ship/v10"">"
'xmlString = xmlString & " <soapenv:Header/>"
'xmlString = xmlString & " <soapenv:Body>"
'xmlString = xmlString & " <v10:ProcessShipmentRequest>"
'xmlString = xmlString & " <v10:WebAuthenticationDetail>"
'xmlString = xmlString & " <v10:UserCredential>"
'xmlString = xmlString & " <v10:Key>ghHTCkqarvQFttGp</v10:Key>"
'xmlString = xmlString & " <v10:Password>4ncFjTPEw8I4RY3gxr79i8qCW</v10:Password>"
'xmlString = xmlString & " </v10:UserCredential>"
'xmlString = xmlString & " </v10:WebAuthenticationDetail>"
'xmlString = xmlString & " <v10:ClientDetail>"
'xmlString = xmlString & " <v10:AccountNumber>510087402</v10:AccountNumber>"
'xmlString = xmlString & " <v10:MeterNumber>118553184</v10:MeterNumber>"
'xmlString = xmlString & " <!--Optional:-->"
'xmlString = xmlString & " <v10:IntegratorId>123</v10:IntegratorId>"
'xmlString = xmlString & " <!--Optional:-->"
'xmlString = xmlString & " <v10:Localization>"
'xmlString = xmlString & " <v10:LanguageCode>EN</v10:LanguageCode>"
'xmlString = xmlString & " <!--Optional:-->"
'xmlString = xmlString & " <v10:LocaleCode>ES</v10:LocaleCode>"
'xmlString = xmlString & " </v10:Localization>"
'xmlString = xmlString & " </v10:ClientDetail>"
'xmlString = xmlString & " <!--Optional:-->"
'xmlString = xmlString & " <v10:TransactionDetail>"
'xmlString = xmlString & " <!--Optional:-->"
'xmlString = xmlString & " <v10:CustomerTransactionId>PROCESS SHIPMENT</v10:CustomerTransactionId>"
'xmlString = xmlString & " <!--Optional:-->"
'xmlString = xmlString & " <v10:Localization>"
'xmlString = xmlString & " <v10:LanguageCode>EN</v10:LanguageCode>"
'xmlString = xmlString & " <!--Optional:-->"
'xmlString = xmlString & " <v10:LocaleCode>ES</v10:LocaleCode>"
'xmlString = xmlString & " </v10:Localization>"
'xmlString = xmlString & " </v10:TransactionDetail>"
'xmlString = xmlString & " <v10:Version>"
'xmlString = xmlString & " <v10:ServiceId>ship</v10:ServiceId>"
'xmlString = xmlString & " <v10:Major>10</v10:Major>"
'xmlString = xmlString & " <v10:Intermediate>0</v10:Intermediate>"
'xmlString = xmlString & " <v10:Minor>0</v10:Minor>"
'xmlString = xmlString & " </v10:Version>"
'xmlString = xmlString & " <v10:RequestedShipment>"
'xmlString = xmlString & " <v10:ShipTimestamp>" & SDate & "</v10:ShipTimestamp>"
'xmlString = xmlString & " <v10:DropoffType>REGULAR_PICKUP</v10:DropoffType>"
'xmlString = xmlString & " <v10:ServiceType>PRIORITY_OVERNIGHT</v10:ServiceType>"
'xmlString = xmlString & " <v10:PackagingType>YOUR_PACKAGING</v10:PackagingType>"
'xmlString = xmlString & " <v10:Shipper>"
'xmlString = xmlString & " <v10:AccountNumber>510087402</v10:AccountNumber>"
'xmlString = xmlString & " <v10:Tins>"
'xmlString = xmlString & " <v10:TinType>PERSONAL_STATE</v10:TinType>"
'xmlString = xmlString & " <v10:Number>1057</v10:Number>"
'xmlString = xmlString & " <v10:Usage>ShipperTinsUsage</v10:Usage>"
'xmlString = xmlString & " </v10:Tins>"
'xmlString = xmlString & " <v10:Contact>"
'xmlString = xmlString & " <v10:ContactId>RBB1057</v10:ContactId>"
'xmlString = xmlString & " <v10:PersonName>N/A</v10:PersonName>"
'xmlString = xmlString & " <v10:Title></v10:Title>"
'xmlString = xmlString & " <v10:CompanyName>N/A</v10:CompanyName>"
'xmlString = xmlString & " <v10:PhoneNumber>4166548008</v10:PhoneNumber>"
'xmlString = xmlString & " <v10:PhoneExtension></v10:PhoneExtension>"
'xmlString = xmlString & " <v10:PagerNumber></v10:PagerNumber>"
'xmlString = xmlString & " <v10:FaxNumber>4166543663</v10:FaxNumber>"
'xmlString = xmlString & " <v10:EMailAddress>jeff.fournier@microforum.ca</v10:EMailAddress>"
'xmlString = xmlString & " </v10:Contact>"
'xmlString = xmlString & " <v10:Address>"
'xmlString = xmlString & " <v10:StreetLines>N/A</v10:StreetLines>"
'xmlString = xmlString & " <v10:StreetLines>N/A</v10:StreetLines>"
'xmlString = xmlString & " <v10:City>" & "Mississauga" & "</v10:City>"
'xmlString = xmlString & " <v10:StateOrProvinceCode>" & "ON" & "</v10:StateOrProvinceCode>"
'xmlString = xmlString & " <v10:PostalCode>" & "L4W5K6" & "</v10:PostalCode>"
'xmlString = xmlString & " <v10:UrbanizationCode>CO</v10:UrbanizationCode>"
'xmlString = xmlString & " <v10:CountryCode>" & "CA" & "</v10:CountryCode>"
'xmlString = xmlString & " <v10:Residential>0</v10:Residential>"
'xmlString = xmlString & " </v10:Address>"
'xmlString = xmlString & " </v10:Shipper>"
'xmlString = xmlString & " <v10:Recipient>"
'xmlString = xmlString & " <v10:AccountNumber></v10:AccountNumber>"
'xmlString = xmlString & " <v10:Tins>"
'xmlString = xmlString & " <v10:TinType>PERSONAL_STATE</v10:TinType>"
'xmlString = xmlString & " <v10:Number>0705</v10:Number>"
'xmlString = xmlString & " <v10:Usage>RecipientTinsUsage</v10:Usage>"
'xmlString = xmlString & " </v10:Tins>"
'xmlString = xmlString & " <v10:Contact>"
'xmlString = xmlString & " <v10:ContactId>DRB0705</v10:ContactId>"
'xmlString = xmlString & " <v10:PersonName>DEOYANEE BHADANE</v10:PersonName>"
'xmlString = xmlString & " <v10:Title>Ms.</v10:Title>"
'xmlString = xmlString & " <v10:CompanyName>DEOYAROHIT0705$</v10:CompanyName>"
'xmlString = xmlString & " <v10:PhoneNumber>9762308621</v10:PhoneNumber>"
'xmlString = xmlString & " <v10:PhoneExtension>02033469</v10:PhoneExtension>"
'xmlString = xmlString & " <v10:PagerNumber>9762308621</v10:PagerNumber>"
'xmlString = xmlString & " <v10:FaxNumber>9762308621</v10:FaxNumber>"
'xmlString = xmlString & " <v10:EMailAddress>Deoyanee*Bhadane@DeoyaRohit.com</v10:EMailAddress>"
'xmlString = xmlString & " </v10:Contact>"
'xmlString = xmlString & " <v10:Address>"
'xmlString = xmlString & " <v10:StreetLines>" & "212 Delorain Ave" & "</v10:StreetLines>"
'xmlString = xmlString & " <v10:StreetLines>RECIPIENT ADDRESS LINE 2</v10:StreetLines>"
'xmlString = xmlString & " <v10:City>" & "Toronto" & "</v10:City>"
'xmlString = xmlString & " <v10:StateOrProvinceCode>" & "ON" & "</v10:StateOrProvinceCode>"
'xmlString = xmlString & " <v10:PostalCode>" & "M5M 2B3" & "</v10:PostalCode>"
'xmlString = xmlString & " <v10:UrbanizationCode>CO</v10:UrbanizationCode>"
'xmlString = xmlString & " <v10:CountryCode>" & "CA" & "</v10:CountryCode>"
'xmlString = xmlString & " <v10:Residential>0</v10:Residential>"
'xmlString = xmlString & " </v10:Address>"
'xmlString = xmlString & " </v10:Recipient>"
'xmlString = xmlString & " <v10:RecipientLocationNumber>DEN001</v10:RecipientLocationNumber>"
'xmlString = xmlString & " <v10:Origin>"
'xmlString = xmlString & " <v10:Contact>"
'xmlString = xmlString & " <v10:ContactId>RBB1057</v10:ContactId>"
'xmlString = xmlString & " <v10:PersonName>ROHIT BORSE</v10:PersonName>"
'xmlString = xmlString & " <v10:Title>Mr.</v10:Title>"
'xmlString = xmlString & " <v10:CompanyName>DEOYAROHIT0705$</v10:CompanyName>"
'xmlString = xmlString & " <v10:PhoneNumber>9762308621</v10:PhoneNumber>"
'xmlString = xmlString & " <v10:PhoneExtension>02033469</v10:PhoneExtension>"
'xmlString = xmlString & " <v10:PagerNumber>9762308621</v10:PagerNumber>"
'xmlString = xmlString & " <v10:FaxNumber>9762308621</v10:FaxNumber>"
'xmlString = xmlString & " <v10:EMailAddress>Rohit*Borse@DeoyaRohit.com</v10:EMailAddress>"
'xmlString = xmlString & " </v10:Contact>"
'xmlString = xmlString & " <v10:Address>"
'xmlString = xmlString & " <v10:StreetLines>" & "5985 Explorer Dr" & "</v10:StreetLines>"
'xmlString = xmlString & " <v10:StreetLines>ORIGIN ADDRESS LINE 2</v10:StreetLines>"
'xmlString = xmlString & " <v10:City>" & "Mississauga" & "</v10:City>"
'xmlString = xmlString & " <v10:StateOrProvinceCode>" & "ON" & "</v10:StateOrProvinceCode>"
'xmlString = xmlString & " <v10:PostalCode>" & "L4W 5K6" & "</v10:PostalCode>"
'xmlString = xmlString & " <v10:UrbanizationCode>CO</v10:UrbanizationCode>"
'xmlString = xmlString & " <v10:CountryCode>" & "CA" & "</v10:CountryCode>"
'xmlString = xmlString & " <v10:Residential>0</v10:Residential>"
'xmlString = xmlString & " </v10:Address>"
'xmlString = xmlString & " </v10:Origin>"
'xmlString = xmlString & " <v10:ShippingChargesPayment>"
'xmlString = xmlString & " <v10:PaymentType>SENDER</v10:PaymentType>"
'xmlString = xmlString & " <v10:Payor>"
'xmlString = xmlString & " <v10:AccountNumber>510087887</v10:AccountNumber>"
'xmlString = xmlString & " <v10:CountryCode>US</v10:CountryCode>"
'xmlString = xmlString & " </v10:Payor>"
'xmlString = xmlString & " </v10:ShippingChargesPayment>"
'xmlString = xmlString & " <v10:DeliveryInstructions>DeliveryInstructions</v10:DeliveryInstructions>"
'xmlString = xmlString & " <v10:BlockInsightVisibility>1</v10:BlockInsightVisibility>"
'xmlString = xmlString & " <v10:ErrorLabelBehavior>STANDARD</v10:ErrorLabelBehavior>"
'xmlString = xmlString & " <v10:LabelSpecification>"
'xmlString = xmlString & " <v10:LabelFormatType>COMMON2D</v10:LabelFormatType>"
'xmlString = xmlString & " <v10:ImageType>PNG</v10:ImageType>"
'xmlString = xmlString & " <v10:LabelStockType>PAPER_4X6</v10:LabelStockType>"
'xmlString = xmlString & " <v10:LabelPrintingOrientation>TOP_EDGE_OF_TEXT_FIRST</v10:LabelPrintingOrientation>"
'xmlString = xmlString & " </v10:LabelSpecification>"
'xmlString = xmlString & " <v10:RateRequestTypes>ACCOUNT</v10:RateRequestTypes>"
'xmlString = xmlString & " <v10:PackageCount>1</v10:PackageCount>"
'xmlString = xmlString & " <v10:RequestedPackageLineItems>"
'xmlString = xmlString & " <v10:SequenceNumber>1</v10:SequenceNumber>"
'xmlString = xmlString & " <v10:GroupNumber>1</v10:GroupNumber>"
'xmlString = xmlString & " <v10:GroupPackageCount>1</v10:GroupPackageCount>"
'xmlString = xmlString & " <v10:Weight>"
'xmlString = xmlString & " <v10:Units>" & "LBS" & "</v10:Units>"
'xmlString = xmlString & " <v10:Value>" & "11" & "</v10:Value>"
'xmlString = xmlString & " </v10:Weight>"
'xmlString = xmlString & " <v10:Dimensions>"
'xmlString = xmlString & " <v10:Length>" & "10" & "</v10:Length>"
'xmlString = xmlString & " <v10:Width>" & "10" & "</v10:Width>"
'xmlString = xmlString & " <v10:Height>" & "10" & "</v10:Height>"
'xmlString = xmlString & " <v10:Units>" & "IN" & "</v10:Units>"
'xmlString = xmlString & " </v10:Dimensions>"
'xmlString = xmlString & " <v10:PhysicalPackaging>BAG</v10:PhysicalPackaging>"
'xmlString = xmlString & " <v10:ItemDescription>ItemDescription</v10:ItemDescription>"
'xmlString = xmlString & " <v10:CustomerReferences>"
'xmlString = xmlString & " <v10:CustomerReferenceType>CUSTOMER_REFERENCE</v10:CustomerReferenceType>"
'xmlString = xmlString & " <v10:Value>CUSTREF20110408T105646</v10:Value>"
'xmlString = xmlString & " </v10:CustomerReferences>"
'xmlString = xmlString & " <v10:CustomerReferences>"
'xmlString = xmlString & " <v10:CustomerReferenceType>P_O_NUMBER</v10:CustomerReferenceType>"
'xmlString = xmlString & " <v10:Value>PO20110408T105646</v10:Value>"
'xmlString = xmlString & " </v10:CustomerReferences>"
'xmlString = xmlString & " <v10:CustomerReferences>"
'xmlString = xmlString & " <v10:CustomerReferenceType>INVOICE_NUMBER</v10:CustomerReferenceType>"
'xmlString = xmlString & " <v10:Value>IO20110408T105646</v10:Value>"
'xmlString = xmlString & " </v10:CustomerReferences>"
'xmlString = xmlString & " <v10:ContentRecords>"
'xmlString = xmlString & " <v10:PartNumber>PRTNUM01</v10:PartNumber>"
'xmlString = xmlString & " <v10:ItemNumber>ITMNUM01</v10:ItemNumber>"
'xmlString = xmlString & " <v10:ReceivedQuantity>15</v10:ReceivedQuantity>"
'xmlString = xmlString & " <v10:Description>ContentDescription</v10:Description>"
'xmlString = xmlString & " </v10:ContentRecords>"
'xmlString = xmlString & " </v10:RequestedPackageLineItems>"
'xmlString = xmlString & " </v10:RequestedShipment>"
'xmlString = xmlString & " </v10:ProcessShipmentRequest>"
'xmlString = xmlString & " </soapenv:Body>"
'xmlString = xmlString & "</soapenv:Envelope>"
'
'
'
'
'
'
'
'    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & "TestTransRequest.xml", True)
'        .Write xmlString
'        .Close
'    End With
'
'    g_strConfigHostEnvironment = "beta" ' g_objSettings.Setting("API_ENVIRONMENT")
'    strTrans = SendSOAPToGateway(xmlString)
'
'    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & "TestTransReply.xml", True)
'        .Write strTrans
'        .Close
'    End With
'    Set xmlReply = New DOMDocument
'    xmlReply.loadXML strTrans
'
'    If 0 <> InStr(1, strTrans, "<v10:Image>") Then
'
'        strTrans = Mid(strTrans, InStr(1, strTrans, "<v10:Image>"), Len(strTrans))
'        strTrans = Left(strTrans, InStr(1, strTrans, "</v10:Image>") - 1)
'        strTrans = Right(strTrans, Len(strTrans) - Len("<v10:Image>"))
'        ' Use any Base64 coder/decoder
'        strInsert = DecodeStr64(strTrans)
'        ' Temporary label file
'        intFile = FreeFile
'        strLabelFilename = "TestTrans" & "Label"
'        strLabelFilename = Replace(strLabelFilename, "/", "_")
'        strLabelFilename = Replace(strLabelFilename, "\", "_")
'        strLabelFilename = Replace(strLabelFilename, ".", "_")
'        If g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PDF" Then
'            strLabelFilename = strLabelFilename & ".pdf"
'        ElseIf g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PNG" Then
'            strLabelFilename = strLabelFilename & ".png"
'        Else
'            strLabelFilename = strLabelFilename & ".txt"
'        End If
'        Open g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename For Output As #intFile
'        Print #intFile, strInsert
'        Close #intFile
'
'        If g_objSettings.Setting("PRINTER") <> "" Then
'            If g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PDF" Then
'                Shell """" & g_objSettings.SettingWithDefault("AdobeReaderPath", "AcroRd32.exe") & """" & " /t" _
'                    & " " & """" & g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename & """" _
'                    & " " & """" & g_objSettings.Setting("PRINTER") & """" & "", vbNormalFocus
'            ElseIf g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PNG" Then
'                Shell """C:\Program Files\Internet Explorer\iexplore.exe"" """ & g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename & """", vbNormalFocus
'            Else
'                g_objFSO.CopyFile g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename, g_objSettings.Setting("PRINTER")
'            End If
'        End If
'
'
'    End If
'
'
'End Sub

Public Sub TestTrans()

    
    Dim intFile As Long
    Dim strInsert As String

    Dim i As Long
    Dim strCountry As String
    Dim strTrans As String
    Dim strMasterAwb, strFormId, strPackageCnt As String
    Dim strOption() As String
    Dim strCommodity() As String
    Dim obj As Object
    Dim xmlnode1 As IXMLDOMNode
    Dim xmlNode2 As IXMLDOMNode
    Dim xmlNode3 As IXMLDOMNode
    Dim xmlNode4 As IXMLDOMNode
    Dim xmlNode5 As IXMLDOMNode
    Dim xmlNode6 As IXMLDOMNode
    Dim xmlNode7 As IXMLDOMNode
    
    If g_objFSO Is Nothing Then Set g_objFSO = New FileSystemObject
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    Set xmlDoc = New DOMDocument

    xmlDoc.Load ("C:\Documents and Settings\563774\My Documents\request.xml")
    
    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & "TestTransRequest.xml", True)
        .Write xmlDoc.FirstChild.XML
        .Close
    End With

    g_strConfigHostEnvironment = "beta" ' g_objSettings.Setting("API_ENVIRONMENT")
    strTrans = SendRequestToGateway(xmlDoc.FirstChild.XML)

    With g_objFSO.CreateTextFile(g_objSettings.Setting("LOG_FOLDER") & "TestTransReply.xml", True)
        .Write strTrans
        .Close
    End With
    Set xmlReply = New DOMDocument
    xmlReply.loadXML strTrans

    If 0 <> InStr(1, strTrans, "<v10:Image>") Then
        
        strTrans = Mid(strTrans, InStr(1, strTrans, "<v10:Image>"), Len(strTrans))
        strTrans = Left(strTrans, InStr(1, strTrans, "</v10:Image>") - 1)
        strTrans = Right(strTrans, Len(strTrans) - Len("<v10:Image>"))
        ' Use any Base64 coder/decoder
        strInsert = DecodeStr64(strTrans)
        ' Temporary label file
        intFile = FreeFile
        strLabelFilename = "TestTrans" & "Label"
        strLabelFilename = Replace(strLabelFilename, "/", "_")
        strLabelFilename = Replace(strLabelFilename, "\", "_")
        strLabelFilename = Replace(strLabelFilename, ".", "_")
        If g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PDF" Then
            strLabelFilename = strLabelFilename & ".pdf"
        ElseIf g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PNG" Then
            strLabelFilename = strLabelFilename & ".png"
        Else
            strLabelFilename = strLabelFilename & ".txt"
        End If
        Open g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename For Output As #intFile
        Print #intFile, strInsert
        Close #intFile
    
        If g_objSettings.Setting("PRINTER") <> "" Then
            If g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PDF" Then
                Shell """" & g_objSettings.SettingWithDefault("AdobeReaderPath", "AcroRd32.exe") & """" & " /t" _
                    & " " & """" & g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename & """" _
                    & " " & """" & g_objSettings.Setting("PRINTER") & """" & "", vbNormalFocus
            ElseIf g_objSettings.SettingWithDefault("LABEL_IMAGE_TYPE", "ZPLII") = "PNG" Then
                Shell """C:\Program Files\Internet Explorer\iexplore.exe"" """ & g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename & """", vbNormalFocus
            Else
                g_objFSO.CopyFile g_objSettings.Setting("LABEL_FOLDER") & strLabelFilename, g_objSettings.Setting("PRINTER")
            End If
        End If
        
        
    End If


End Sub

Public Sub EditSettings()

    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    g_objSettings.EditSettings

End Sub

Public Sub SaveSettings()

    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    If Not g_objSettings Is Nothing Then
        g_objSettings.SaveSettings
    End If
    
End Sub

Public Sub SetProfile(ProfileName As String)

    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    g_objSettings.ProfileKey = ProfileName
    
End Sub

Public Function Cell(CellRow As Long, ColumnName As String, Optional ByVal SetValue As String) As String

    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    If mobjWorksheet Is Nothing Then Set mobjWorksheet = ActiveWorkbook.ActiveSheet
    If Not IsEmpty(SetValue) Then
        mobjWorksheet.Cells(CellRow, SheetColumn(g_objSettings.Setting(ColumnName))) = SetValue
    End If
    Cell = mobjWorksheet.Cells(CellRow, SheetColumn(g_objSettings.Setting(ColumnName)))

End Function

Public Function SetSetting(SettingName As String, Optional ByVal SetValue As String = "WTF") As String

    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    If SetValue <> "WTF" Then
        g_objSettings.Setting(SettingName) = SetValue
    End If
    SetSetting = g_objSettings.Setting(SettingName)
    g_objSettings.SaveSettings
    
End Function

Public Sub TestFunction()
    
    Dim i As Long, j As Long
    Dim strTrackNo As String
    Dim intFile As Long
    
    If g_objSettings Is Nothing Then Set g_objSettings = New classSettings
    
    For i = Selection.Column To (Selection.Column + Selection.Columns.Count - 1)

            Set mobjWorksheet = ActiveSheet
            
            For j = Selection.Row To (Selection.Row + Selection.Rows.Count - 1)

                ActiveSheet.Cells(j, i) = CountryConv(ActiveSheet.Cells(j, 1))

            Next j

    Next i


End Sub


